Find All References to an Oracle Table

Question:  I need a query to find all tables that reference my table.  I tried to delete my table but I got a foreign key constraint error:  

Evidently this is an Oracle DELETE TABLE constraint violation. 

How do I find all tables that reference my table?

Answer:  I have complete notes on using the drop table command. 

You can check for foreign key constraint errors by looking at the dba_constraints and the dba_cons_columns views. A view called dba_cross_refs provides cross reference  information on a system-wide basis and user_cross_refs shows only those external references for a specific schema.

 Using the cascade constraints clause in a drop table will force a cascade delete to occur in all child tables.

Oracle also has the drop table if exists feature.

There are several data dictionary views that allow you to find all foreign reference to an Oracle table, namely the dba_constraints and dba_cons_columns views.  

Here is one script to display all references to a table using a non-correlated subquery:

select *
from
    all_constraints
where
    r_constraint_name in
    (select       constraint_name
    from
       all_constraints
    where
       table_name='MYTAB')
;  

This SQL will show all dependent tables for a table, all reference to the table within other tables:

select
   cons.owner            as child_owner,
   cons.table_name       as child_table,
   cons.constraint_name     constaint_name,
   cons.constraint_type     constraint_type,
   col.owner                parent_owner,
   col.table_name           parent_table,
   col.column_name          column_name
from

   see BC Oracle code depot for alternate scripts.
   dba_cons_columns      col,
   dba_constraints       cons
where
   cons.r_owner = col.owner
and
   cons.r_constraint_name = col.constraint_name
and
   cons.table_name = '<TABLE NAME>'

You can also run a query to find all tables within a PL/SQL procedure.  

There are many other ways to see all related tables and you can download working scripts at the BC Oracle code depot.

Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright ? 1996 -  2012  All rights reserved. Oracle ? is the registered trademark of Oracle Corporation.