Find tables without foreign keys in Oracle database

Ania - Dataedo Team Ania 2019-06-04

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept