How to get all tables that have FKs to another table?

Question

Is there any way to get all tables that have foreign keys to another table in oracle with a query?

Answer 1

Here is a good article with an answer:

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints 
where constraint_type='R'
and r_constraint_name in (select constraint_name from all_constraints 
where constraint_type in ('P','U') and table_name='TABLE_NAME');

Answer 2

Assuming that both the parent and child tables are in the same schema:

select t1.table_name child_table, t1.constraint_name, t2.table_name parent_table

from user_constraints t1, user_constraints t2

where t1.r_constraint_name = t2.constraint_name

Note that r_constraint_name is populated only for FK (type 'R') constraints, so the self-join only returns info of interest