Re: Question about foreign keys

From: <bwillden_at_flash.net>
Date: 1996/05/28
Message-ID: <31ABA1B5.60CC_at_flash.net>

Craig M. Wall wrote:
>
> duffy_at_cais.cais.com (MCC) wrote:
>
> >Hi All,
 

> > I have a foreign key and need to find the table/column
> >it is referencing, I've tried DBA_CONS_COLUMNS and DBA_CONSTRAINTS
> >and could not get the clue, please help.
 

> >Thanks,
> >MCC
>
> I use the following code to create a spool file to examine.
> It lists Tables with foreign keys and which column in the
> Referenced table's primary key the FK is pointing to.
> It works OK for single columns keys, haven't tried it
> yet on multiples.
> ==================
> set termout off
> spool on
> set linesize 132
> column constraint_name format a20 heading "Foreign Key"
> column table_name format a30 heading "Table"
> column constraint_type heading "T"
> column stat format a1 heading "Status"
> column r_constraint_name format a15 heading "Primary Key"
> break on table
> break on r_constraint_name
> break on b.table_name
> select a.table_name, b.r_constraint_name,
> b.table_name, b.constraint_name, b.constraint_type,
> substr(b.status,1,1) stat
> from user_constraints a, user_constraints b
> where b.r_constraint_name = a.constraint_name
> and b.constraint_type ='R'
> order by a.table_name, r_constraint_name, b.table_name;
> spool off
>
> # now print the spooled file (e.g. on.lis,on.lst)
> and you will have a map of the FK & PK relations.
>
> Hope this helps.
>
> Craig M. Wall

Or Try this one inside SQL*Plus:

rem Cons3.sql - Shows Foreign Key / Reference Constraints rem
rem Bruce L. Willden, USPA&IRA
rem 22 Sep 94
rem
Prompt Display Foreign Key/Reference Constraints. Prompt .
Accept IN_OWNER CHAR PROMPT 'Input Table Owner : ' Accept IN_NAME CHAR PROMPT 'Table (wildcards ok) or ENTER for all Tables : '
Prompt .
set serveroutput on
set feedback off
set verify off
spool ref_cons.&IN_OWNER
Prompt Display Foreign Key/Reference Constraints for &IN_OWNER

declare

   cursor c1 is

         SELECT C.NAME CONSTR, C1.NAME REF_CONSTR,
                O.NAME TN1, COL.NAME COLUMN_NAME, 
                O1.NAME TN2, COL1.NAME REF_COL, 
                DECODE(D.ENABLED, NULL, 'DISABLED','ENABLED') STATUS, 
                CC.POS# POSITION, CC1.POS# REF_POSITION,
                U.NAME CONSTR_OWNER, U1.NAME REF_OWNER,
		decode(D.type, 4,
                decode(D.refact, 1, 'CASCADE', 'NO ACTION'), NULL) 
ON_DELETE
           FROM SYS.USER$ U,  SYS.USER$ U1,
                SYS.CON$ C,   SYS.CON$ C1,
                SYS.COL$ COL, SYS.COL$ COL1,
                SYS.CCOL$ CC, SYS.CCOL$ CC1,
                SYS.OBJ$ O,   SYS.OBJ$ O1,
                SYS.CDEF$ D,  SYS.CDEF$ D1
          WHERE D.TYPE    = 4
            AND C.OWNER#  = U.USER#
            AND C1.OWNER# = U1.USER#
            AND C.CON#    = D.CON#
            AND D.OBJ#    = O.OBJ#
            AND D.RCON#   = D1.CON#
            AND C1.CON#   = D1.CON#
            AND D1.OBJ#   = O1.OBJ#
            AND C.CON#    = CC.CON#
            AND C1.CON#   = CC1.CON#
            AND CC.POS#   = CC1.POS#
            AND CC.COL#   = COL.COL#
            AND CC.OBJ#   = COL.OBJ#
            AND CC1.COL#  = COL1.COL#
            AND CC1.OBJ#  = COL1.OBJ#
            AND (U.NAME   = UPPER('&IN_OWNER')
                 OR U1.NAME   = UPPER('&IN_OWNER'))
            AND (O.NAME LIKE 
decode('&IN_NAME',NULL,'%',UPPER('&IN_NAME'))
                 OR O1.NAME LIKE 
decode('&IN_NAME',NULL,'%',UPPER('&IN_NAME')))
         ORDER BY C.NAME, CC.POS#;

   inrec c1%rowtype;
   hconstr varchar2(65);
   F_ONE boolean;
begin

   hconstr := '.';
   DBMS_OUTPUT.ENABLE(100000);
   DBMS_OUTPUT.PUT_LINE('. ');
   F_ONE := TRUE;
   open c1;
   loop

      fetch c1 into inrec;
      exit when c1%NOTFOUND or c1%NOTFOUND is null;
      if hconstr != inrec.constr then
         if F_ONE = FALSE then
           DBMS_OUTPUT.PUT_LINE('. ');
         end if;
         DBMS_OUTPUT.PUT_LINE
               ('TABLE : '||rpad(inrec.TN1,31,' ')||
                'R TABLE   : '||inrec.TN2);
         DBMS_OUTPUT.PUT_LINE
               ('STAT  : '||rpad(inrec.status,31,' ')||
		'ON DELETE : '||inrec.ON_DELETE);
         DBMS_OUTPUT.PUT_LINE
               ('CONST : '||rpad(inrec.constr,31,' ')||
                'R CONST   : '||inrec.ref_constr);
         DBMS_OUTPUT.PUT_LINE
               ('OWNER : '||rpad(inrec.constr_owner,31,' ')||
                'R OWNER   : '||inrec.ref_owner);
         hconstr := inrec.constr;
         F_ONE   := FALSE;
      end if;
      DBMS_OUTPUT.PUT_LINE('COL'||
                 TO_CHAR(inrec.POSITION,'90')||': '||
                 rpad(inrec.COLUMN_NAME,31,' ')||'R COL'||
                 TO_CHAR(inrec.REF_POSITION,'90')||'  : '||
                 inrec.REF_COL);

   end loop;
   DBMS_OUTPUT.PUT_LINE('. ');
end;
/
set serveroutput off
spool off
prompt .
prompt Output generated to file ref_cons.&IN_OWNER prompt For PC/Windows Users, name will be ref_cons.XXX prompt where XXX is first three digits of '&IN_OWNER' prompt . Received on Tue May 28 1996 - 00:00:00 CEST