Re: Help (2nd request) with determining parent key foreign key relationship

Here is what you want:

set verify off linesize 120
ACCEPT tab_n PROMPT 'Enter the table name: '

col "FK Name" form A15 word_wrapped
col "PK Name" form A15 word_wrapped
col "Table Name" format a25
col "PK Table Name" format a25
col "Column Name" format a25 word_wrapped
col "FK Status" format a9

select u.table_name "Table Name", u.constraint_name "FK Name", u.columns "Column Name",
w.r_constraint_name "PK Name", x.table_name "PK Table Name", u.status "FK Status"
from
(select a.table_name, a.constraint_name, a.status,

             max(decode(position, 1, substr(column_name,1,30),NULL)) ||

             max(decode(position, 2,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 3,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 4,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 5,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 6,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 7,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 8,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 9,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,10,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,11,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,12,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,13,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,14,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,15,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,16,',
'||substr(column_name,1,30),NULL)) columns
from user_cons_columns b, user_constraints a where a.table_name = UPPER('&tab_n') and a.constraint_name = b.constraint_name
and a.constraint_type = 'R'
group by a.table_name, a.constraint_name, a.status) u, user_constraints w,
user_constraints x
where u.constraint_name=w.constraint_name and w.r_constraint_name=x.constraint_name
/

If you are DBA want list all the FK info for a schema, use this one.

set verify off linesize 120
ACCEPT user_n PROMPT 'Enter the username of owner: '

col "FK Name" form A15 word_wrapped
col "PK Name" form A15 word_wrapped
col "Table Name" format a25
col "PK Table Name" format a25
col "Column Name" format a25 word_wrapped
col "FK Status" format a9

select u.table_name "Table Name", u.constraint_name "FK Name", u.columns "Column Name",
w.r_constraint_name "PK Name", x.table_name "PK Table Name", u.status "FK Status"
from
(select a.table_name, a.constraint_name, a.status,

             max(decode(position, 1, substr(column_name,1,30),NULL)) ||

             max(decode(position, 2,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 3,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 4,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 5,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 6,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 7,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 8,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position, 9,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,10,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,11,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,12,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,13,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,14,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,15,',
'||substr(column_name,1,30),NULL)) ||

             max(decode(position,16,',
'||substr(column_name,1,30),NULL)) columns
from dba_cons_columns b, dba_constraints a where a.owner = UPPER('&user_n') and
a.constraint_name = b.constraint_name
and a.constraint_type = 'R'
group by a.table_name, a.constraint_name, a.status) u, dba_constraints w,
dba_constraints x
where u.constraint_name=w.constraint_name and w.r_constraint_name=x.constraint_name
/ Received on Wed Aug 08 2001 - 15:49:10 CDT