Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help (2nd request) with determining parent key foreign key relationship
Here is the output:
Enter the table name check for foreign key: bonus
FK Column Name PK Table Name PK Column Name
-------------------- ------------------------- -------------------- DEPTNO DEPT DEPTNO EMP_NAME, EMP_ID EMP ENAME, EMPNO ___________________________
Script for single table
set verify off linesize 100
ACCEPT tab_n PROMPT 'Enter the table name check for foreign key: '
col "PK Table Name" format a25 col "FK Column Name" format a20 word_wrapped col "PK Column Name" format a20 word_wrapped
select u.columns "FK Column Name", x.table_name "PK Table Name", x.columns "PK Column Name"
from
(select a.constraint_name, a.r_constraint_name,
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.constraint_name,a.r_constraint_name) u,
(select a.table_name, a.constraint_name,
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
b.constraint_name = a.constraint_name
and a.constraint_type = 'P'
group by a.table_name, a.constraint_name) x
where u.r_constraint_name=x.constraint_name
/
Script for DBA
set verify off linesize 130
ACCEPT user_n PROMPT 'Enter the username of owner: '
col "FK Name" form A15 word_wrapped col "PK Name" form A15 word_wrapped col "FK Table Name" format a25 col "PK Table Name" format a25 col "FK Column Name" format a20 word_wrapped col "PK Column Name" format a20 word_wrapped
select u.table_name "FK Table Name", u.columns "FK Column Name", x.table_name "PK Table Name", x.columns "PK Column Name", u.constraint_name "FK Name", u.r_constraint_name "PK Name" from (select a.table_name, a.constraint_name, a.r_constraint_name,
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.r_constraint_name) u,
(select a.table_name, a.constraint_name,
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.constraint_name = b.constraint_name
and a.constraint_type = 'P'
group by a.table_name, a.constraint_name) x
where u.r_constraint_name=x.constraint_name
/
Received on Thu Aug 09 2001 - 12:30:22 CDT