Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help (2nd request) with determining parent key foreign key relationship

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

From: OK <j_p_x_at_hotmail.com>
Date: 9 Aug 2001 10:30:22 -0700
Message-ID: <8daca8eb.0108090930.6e6aa090@posting.google.com>

  1. My previous scripts list ALL FOREIGN KEY columns, not PK cols.
  2. It works no matter how many FKs a table has.
  3. I simplified a little bit, it gives out all the info for sys schema in less than 1 sec.
  4. To list the PK cols, you only need to do slightly modification (see below).
  5. For DBA, I still think the 2nd one is much, much userful.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US