Tag: Find Foreign Key Relationship

The below query lists all of the foreign keys and the parent table and columns to which they relate.

SELECT a.owner , a.table_name , c.column_name ,
 b.owner , b.table_name , d.column_name 
FROM dba_constraints a, dba_constraints b,
 dba_cons_columns c, dba_cons_columns d
WHERE a.r_constraint_name = b.constraint_name
 AND a.constraint_type = 'R'
 AND b.constraint_type = 'P'
 AND a.r_owner=b.owner
 AND a.constraint_name = c.constraint_name
 AND b.constraint_name=d.constraint_name
 AND a.owner = c.owner
 AND a.table_name=c.table_name
 AND b.owner = d.owner
 AND b.table_name=d.table_name;

Source