The Oracle (tm) Users' Co-Operative FAQ

How do I list the parent tables, and relevant columns for a table at the child end of a foreign key constraint .


Author's name: William O'Reilly

Author's Email: boreilly@att.com

Date written: 2nd November, 2001

Oracle version(s): 8.1

How do I list the parent tables, and relevant columns for a table at the child end of a foreign key constraint .

Back to index of questions


The following script will list all parent/child relationships, givng the column names at the parent end of the relation in the correct order.

-- Parent / Child / Key Reference --

set pages 50000
set feedback off
spool constraint.log

col parent format a27
col child  format a27
col key    format a21

select a.table_name         Parent,
       b.table_name         Child,
       column_name          Key,
       substr(position,1,1) P
from 
      user_constraints a, user_constraints b, user_cons_columns c
where 
      a.constraint_name = b.r_constraint_name
and   a.constraint_name = c.constraint_name
order by 1, 2, 4;

spool off

Further reading: N/A


Back to top

Back to index of questions