Find tables without foreign keys in Oracle database

Query below lists:

(A) all tables accessible to the current user in Oracle database that don't have foreign keys

(B) all tables in Oracle database that don't have foreign keys

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select t.owner as schema_name,
       t.table_name,
       '>- no FKs' as foreign_keys
from sys.all_tables t 
left join (select distinct owner,
                           table_name
           from sys.all_constraints 
           where constraint_type = 'R'
           ) fks
           on t.owner = fks.owner
              and t.table_name = fks.table_name
where fks.table_name is null
-- excluding some Oracle maintained schemas
and t.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
order by t.owner, t.table_name;

B. If you have privilege on dba_tables and dba_constraints

select t.owner as schema_name,
       t.table_name,
       '>- no FKs' as foreign_keys
from sys.dba_tables t 
left join (select distinct owner,
                           table_name
           from sys.dba_constraints 
           where constraint_type = 'R'
           ) fks
           on t.owner = fks.owner
              and t.table_name = fks.table_name
where fks.table_name is null
-- excluding some Oracle maintained schemas
and t.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
order by t.owner, t.table_name;

Columns

  • schema_name - table owner, schema name
  • table_name - table name
  • foreign_keys - symbol indicating lack of FKs

Rows

  • One row represents one table that doesn't have a foreign key
  • Scope of rows: (A) all tables accessible to the current user in Oracle database that don't have foreign keys (do not refer to other tables), (B) all tables in Oracle database that don't have foreign keys (do not refer to other tables)
  • Ordered by schema name, table name

Sample results

0
There are no comments. Click here to write the first comment.