Solved

Script to Determine PK\FK Related Tables

Posted on 2009-05-12
4
1,159 Views
Last Modified: 2013-12-18
Hello,

Can anyone post a SQL or PL\SQL script where a given table and all tables related to it via primary and foreign keys are displayed?

Kinda like a mini ERD from the point of view of a given table outward.

Thanks,

Michael
0
Comment
Question by:michael4606
  • 2
4 Comments
 
LVL 12

Assisted Solution

by:Gibu George
Gibu George earned 100 total points
ID: 24363337
select
table_name,constraint_name
from user_cons_columns where column_name=(
select a.COLUMN_NAME from user_cons_columns a,user_constraints b
where a.TABLE_NAME=b.TABLE_NAME
and b.constraint_type in ('P')
and a.TABLE_NAME=)
and constraint_name like '%FK%'
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 200 total points
ID: 24363446
I wouldn't use the constraint name as basis for looking up foreign keys,  there is no rule that says a constraint must have "FK" in it's name.
Instead use the constraint_type = 'R'  (for referential)


try this...

SELECT   pk.constraint_name primary_key,
         fk.owner fk_owner,
         fk.table_name fk_table,
         fk.constraint_name foreign_key_name
  FROM   dba_constraints pk, dba_constraints fk
 WHERE   fk.constraint_type = 'R'
     AND pk.constraint_type = 'P'
     AND fk.r_owner = pk.owner
     AND fk.r_constraint_name = pk.constraint_name
     AND pk.owner = 'SYS'
     AND pk.table_name = 'REGISTRY$'
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24370315
need anything else?
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 24374483
Many years ago I have prepared such script.
You can concentrate on the needed chapters only.
/******************************************************

** PROCEDURE  put_buffer (v_buffer VARCHAR2)

**

** Internal utility to DBMS_OUTPUT.PUT_LINE

** strings avoiding the 255 character limit

**

*******************************************************/
 
 

PROCEDURE  put_buffer (v_buffer VARCHAR2) IS

  cnt   number;

  buf   VARCHAR2(300);

  v_length NUMBER;

BEGIN

  V_length := length(v_buffer);

   IF V_length <= 250 THEN

           DBMS_OUTPUT.PUT_LINE(v_buffer);

   ELSE

           cnt := 1;

           WHILE cnt < V_length LOOP

              IF (V_length - cnt +1) > 250 THEN

                 buf := SUBSTR(v_buffer,cnt,250);

                 DBMS_OUTPUT.PUT_LINE(buf);

                 cnt := cnt + 250;

              ELSE

                 buf := SUBSTR(v_buffer,cnt,V_length-cnt+1);

                 DBMS_OUTPUT.PUT_LINE(buf);

                 cnt := V_length;

              END IF;       

            END LOOP;

    END IF; 

END put_buffer;

/
 

/**********************************************************************************************

** Procedure for displaying: Table Columns, Table Constraints, FK References, Table Indexes   *

** Input: Schema, Table Name                                                                  *

** Run  as user SYS                                                                           *

** Sample table schema: 'ICWUSER' table name: 'BLOODGAS_CURRENT'                              *

**********************************************************************************************/

PROCEDURE an_tab(p_schema VARCHAR2, p_table VARCHAR2) IS

--

        CURSOR table_columns (v_userid VARCHAR2, v_tabname VARCHAR2) IS

             SELECT col.column_name, SUBSTR(col.data_type,1,10)||'(' 

                 || case when col.data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR2') then col.char_length else col.data_length end

                 || case when col.CHAR_USED = 'B' then ' Bytes' when col.CHAR_USED = 'C' then ' Characters' else '' end || ')' || ' '

                 || com.comments "TYPE"

             FROM dba_tab_columns col, dba_col_comments com

             WHERE col.owner = UPPER(p_schema) AND col.table_name = UPPER(p_table)

               AND col.owner = com.owner(+) AND col.table_name = com.table_name (+) AND col.column_name = com.column_name(+);

--

        CURSOR column_details (v_userid VARCHAR2, v_tabname VARCHAR2) IS

             SELECT column_name, SUBSTR(data_type,1,10) || ' ' || char_length || '  '

                    || DATA_LENGTH ||' Bytes ' || CHAR_LENGTH ||' ' 

                    || case when CHAR_USED = 'B' then ' Bytes' else ' Characters' end as "TYPE"

             FROM dba_tab_columns

             WHERE owner = UPPER(p_schema) AND table_name = UPPER(p_table)

                   AND data_type in ('CHAR', 'VARCHAR2','NCHAR','NVARCHAR2');

--

	CURSOR cons_cur (v_userid VARCHAR2, v_tabname VARCHAR2) IS 

	  SELECT * FROM sys.dba_constraints 

	  WHERE owner = v_userid 

	    AND constraint_type in ('P','U','C','R') 

            AND table_name = v_tabname 

	  ORDER BY constraint_type; 

--

	CURSOR col_cur (con_name VARCHAR2, con_owner VARCHAR2) IS 

	  SELECT * FROM sys.dba_cons_columns 

	  WHERE owner = con_owner 

	    AND constraint_name = con_name 

	  ORDER BY position; 

--

	CURSOR indx_cur (con_name VARCHAR2, ind_own VARCHAR2) IS 

	  SELECT a.* 

	  FROM sys.dba_indexes a, sys.dba_ind_columns b, sys.dba_cons_columns c 

	  WHERE c.constraint_name = con_name 

            AND a.owner = ind_own 

            AND b.index_owner = ind_own 

	    AND c.owner = b.index_owner 

	    AND c.position = 1 

	    AND c.table_name = b.table_name 

	    AND c.column_name = b.column_name 

	    AND b.index_name = a.index_name; 

--

	CURSOR ref_cur (v_userid VARCHAR2, v_tabname VARCHAR2)  IS 

            select c1.owner||'.'||c1.table_name ||'   FOREIGN KEY ('|| c2.column_name

                || ')  REFERENCES '|| c3.owner||'.'||c3.table_name||'('

                || c3.column_name ||')' AS fk_def

            from

                dba_constraints  c1,

                dba_cons_columns c2,

                dba_cons_columns c3

            where

                   c1.owner = v_userid       --'LSMEDICAL' 

               AND c1.constraint_type = 'R'  -- in ('P','U','C','R') 

               AND c1.constraint_name = c2.constraint_name

               AND c1.owner = c2.owner

               AND c1.r_constraint_name = c3.constraint_name

               AND c1.r_owner = c3.owner

               AND c3.table_name = v_tabname;  --'EVENT_CURRENT' 

--

         CURSOR indx_info (v_userid VARCHAR2, v_tabname VARCHAR2)  IS 

              SELECT  SUBSTR(i.index_name, 1,30)  || '  ' || 

                      SUBSTR(ic.column_name,1,20) || ' ' ||

                      ic.column_position  || '  ' ||

                      ic.column_length || '  ' ||

                      i.index_type AS indx_info

              FROM    dba_indexes i, dba_ind_columns ic

              WHERE   i.owner = ic.index_owner

                      AND i.index_name = ic.index_name

                      AND i.table_owner = UPPER(v_userid)

                      AND i.table_name  = UPPER(v_tabname);

--

	CURSOR obj_priv_cur (v_userid VARCHAR2, v_tabname VARCHAR2) IS 

	   SELECT grantee, privilege FROM sys.dba_tab_privs 

	   WHERE owner      = v_userid 

	     AND table_name = v_tabname 

	   ORDER BY grantee; 

--

	CURSOR triggers_cur (v_userid VARCHAR2, v_tabname VARCHAR2) IS 

	   SELECT * FROM sys.dba_triggers 

	   WHERE table_owner= v_userid 

	     AND table_name = v_tabname 

	   ORDER BY TRIGGER_NAME; 

 
 

--

	col_str	VARCHAR2(200); 

	v_user		VARCHAR2(30) := UPPER(p_schema); 

        v_tabname       VARCHAR2(30) := UPPER(p_table);

        v_colname       VARCHAR2(130);  

	v_output	VARCHAR2(480);    -- max of 16 cols at 30 chars each 

        v_dummy         NUMBER := 0; 

	v_delrule	VARCHAR2(4); 

	v_status	VARCHAR2(4); 

        v_excp          NUMBER(1) := 0; 

	v_excptab       VARCHAR2(60) := NULL;  

	srch_cond	VARCHAR2(1000); 

        v_table_type    VARCHAR2(11);

        v_table_comments VARCHAR2(4000);

        buf             VARCHAR2(250);

        cnt             NUMBER;

        v_big           varchar2(32000);

BEGIN 

   DBMS_OUTPUT.ENABLE(1000000);         -- Prevents buffer exceeded error 

   DBMS_OUTPUT.PUT_LINE(CHR(10)); 

   DBMS_OUTPUT.PUT_LINE( 'ANALYZING THE TABLE  '||p_schema||'.'||p_table); 

   DBMS_OUTPUT.PUT_LINE(CHR(10)); 
 

   ------ TABLE COLUMNS ----
 

   DBMS_OUTPUT.PUT_LINE('------TABLE COLUMNS:------'); 

   DBMS_OUTPUT.PUT_LINE(CHR(10));

   FOR c1 IN table_columns (v_user, v_tabname) LOOP 

       begin 

            v_colname := RPAD(c1.column_name,30,' ');

            v_big := v_colname || c1.TYPE;

            put_buffer(v_big); 
 

       end;

   END LOOP;
 

   ------ END TABLE COLUMNS ----
 

   ------ TABLE COMMENTS ----
 

   SELECT  table_type, comments  INTO v_table_type, v_table_comments

   FROM  dba_tab_comments

   WHERE owner = UPPER(p_schema) AND table_name = UPPER(p_table);

   DBMS_OUTPUT.PUT_LINE(CHR(10));
 

   DBMS_OUTPUT.PUT_LINE(v_table_type ||' '||p_schema ||'.'||p_table );
 

            put_buffer(v_table_comments);  
 
 

   ------ END TABLE COMMENTS ----
 

   ------ TABLE LENGTHS ----

   FOR i in 1..3 LOOP

       DBMS_OUTPUT.PUT_LINE(CHR(10)); 

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('------CHAR/VARCHAR2 COLUMNS LENGTHS:------'); 

   DBMS_OUTPUT.PUT_LINE(CHR(10));

   FOR c1 IN column_details (v_user, v_tabname) LOOP 

       begin 

            v_colname := RPAD(c1.column_name,30,' ');

            DBMS_OUTPUT.PUT_LINE( v_colname || c1.TYPE); 
 

       end;

   END LOOP;
 

   ------ END TABLE LENGTHS ----
 

   FOR i in 1..3 LOOP

       DBMS_OUTPUT.PUT_LINE(CHR(10)); 

   END LOOP;
 
 
 
 

   ------CONSTRAINTS:------ 
 

   DBMS_OUTPUT.PUT_LINE('------CONSTRAINTS:------'); 

   DBMS_OUTPUT.PUT_LINE(CHR(10)); 

   FOR c1 IN cons_cur(v_user, v_tabname) LOOP 

     begin 

       srch_cond := substr(c1.search_condition,1,length(c1.search_condition)); 

       -- Dont remove table constraint NOT NULL 

       IF (instr(srch_cond,'NOT NULL') < 1) or 

       (instr(srch_cond,'NOT NULL') IS NULL) THEN 

       BEGIN 

          DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||C1.OWNER||'.'||C1.TABLE_NAME); 

          DBMS_OUTPUT.PUT_LINE('  ADD (CONSTRAINT '||C1.CONSTRAINT_NAME); 

 

          IF c1.constraint_type = 'P' THEN v_output := '    PRIMARY KEY ('; 

          ELSIF c1.constraint_type = 'R' THEN v_output := '    FOREIGN KEY ('; 

          ELSIF c1.constraint_type = 'U' THEN v_output := ' UNIQUE ('; 

          ELSIF c1.constraint_type = 'C' THEN 

	      v_output := '    CHECK ('||c1.search_condition||') '||v_excptab; 

          END IF; 

 

          FOR c2 IN col_cur(c1.constraint_name, c1.owner) LOOP 

	     IF c2.position = 1 THEN 

	        v_output := v_output||c2.column_name; 

	     ELSIF c2.position > 1 THEN 

	        v_output := v_output||', '||c2.column_name; 

	     END IF; 

          END LOOP; 

          v_output := v_output ||')';  

          put_buffer (v_output); 

          IF c1.constraint_type = 'R' THEN 

	      v_output := NULL; 

	     FOR c3 IN col_cur(c1.r_constraint_name, c1.r_owner) LOOP 

	        IF c3.position = 1 THEN 

	           v_output := '    REFERENCES '||c3.owner||'.'||c3.table_name||'('; 

	           v_output := v_output||c3.column_name; 

	        ELSIF c3.position > 1 THEN 

	           v_output := v_output||', '||c3.column_name; 

	        END IF; 

	     END LOOP; 

	     v_output := v_output||') '; 

             put_buffer (v_output); 

	     v_delrule := substr(c1.delete_rule,1,2); 

	     IF v_delrule IS NULL THEN v_output :=  v_excptab ||' )'; 

	     ELSIF v_delrule = 'NO' THEN v_output := v_excptab || ' )'; 

	     ELSIF v_delrule = 'CA' THEN v_output := ' ON DELETE CASCADE'||v_excptab || ')'; 

	     END IF; 

             --DBMS_OUTPUT.PUT_LINE(v_output); 

             put_buffer (v_output);

          END IF; 

 

         FOR c4 IN indx_cur(c1.constraint_name, c1.owner) LOOP 

	    IF c1.constraint_type in ('P','U') THEN 

                DBMS_OUTPUT.PUT_LINE(' USING INDEX ');    

	        DBMS_OUTPUT.PUT_LINE('   pctfree  '||c4.pct_free); 

	        DBMS_OUTPUT.PUT_LINE('   initrans      '||c4.ini_trans); 

	        DBMS_OUTPUT.PUT_LINE('   maxtrans      '||c4.max_trans); 

	        DBMS_OUTPUT.PUT_LINE('   tablespace    '||c4.tablespace_name); 

	        DBMS_OUTPUT.PUT_LINE(' Storage ('); 

	        DBMS_OUTPUT.PUT_LINE('   initial        '||c4.initial_extent); 

	        DBMS_OUTPUT.PUT_LINE('   next           '||c4.next_extent); 

	        DBMS_OUTPUT.PUT_LINE(' minextents     '||c4.min_extents); 

	        DBMS_OUTPUT.PUT_LINE('   maxextents     '||c4.max_extents); 

	        DBMS_OUTPUT.PUT_LINE('   pctincrease    '||c4.pct_increase||') '|| 

                                v_excptab ||')'); 

	    END IF; 

        END LOOP; 

 

        v_output := NULL; 

        v_status := substr(c1.status,1,1); 

        IF v_status = 'E' THEN  

            v_output := ' Status: This constraint '||c1.constraint_name||' was ENABLED'; 

        ELSIF v_status = 'D' THEN  

            v_output :=' Status: This constraint '||c1.constraint_name ||' was DISABLED';  

        END IF; 

   

        -- DBMS_OUTPUT.PUT_LINE(v_output);

        put_buffer (v_output);

        DBMS_OUTPUT.PUT_LINE(CHR(10)); 
 

     END; 

   END IF; 

   EXCEPTION 

     WHEN no_data_found THEN 

       DBMS_OUTPUT.PUT_LINE('No Data Found'); 

     WHEN others THEN 

       DBMS_OUTPUT.PUT_LINE('Other: '||substr(sqlerrm,1,60)); 

       DBMS_OUTPUT.PUT_LINE(c1.constraint_name||' '||c1.constraint_type); 

       DBMS_OUTPUT.PUT_LINE(c1.search_condition); 

   END; 

  END LOOP;
 

        ------ END   CONSTRAINTS   ------ 
 
 

        --------  begin WHO REFERENCES THIS TABLE ---------

        DBMS_OUTPUT.PUT_LINE(CHR(10)); 

        DBMS_OUTPUT.PUT('-------WHO REFERENCES THE TABLE     ');

        DBMS_OUTPUT.PUT(v_user||'.'||v_tabname);

        DBMS_OUTPUT.PUT_LINE('--------');

        DBMS_OUTPUT.PUT_LINE('LIST OF THE TABLES THAT REFERENCE THIS TABLE');

            DBMS_OUTPUT.PUT_LINE(CHR(10));

        FOR c5 IN ref_cur(v_user, v_tabname) LOOP 

	    DBMS_OUTPUT.PUT_LINE(c5.fk_def);

            DBMS_OUTPUT.PUT_LINE(CHR(10));    

        END LOOP;  
 

        --------- end   WHO REFERENCES THIS TABLE --------
 
 

        -------   INDEXES   --------
 

        DBMS_OUTPUT.PUT_LINE('-------INDEXES ON THIS TABLE------');

        DBMS_OUTPUT.PUT_LINE(CHR(10));

        FOR c6 IN indx_info(v_user, v_tabname) LOOP 

	    DBMS_OUTPUT.PUT_LINE(c6.indx_info);     

        END LOOP;  
 
 
 

        ------- END   INDEXES  --------

 

        --------  begin WHO HAS ACCESS TO THIS TABLE ---------

        DBMS_OUTPUT.PUT_LINE(CHR(10)); 

        DBMS_OUTPUT.PUT('-------WHO HAS ACCESS TO THIS TABLE   ');

        DBMS_OUTPUT.PUT(v_user||'.'||v_tabname);

        DBMS_OUTPUT.PUT_LINE('--------');

        DBMS_OUTPUT.PUT_LINE('-------Except the owner of the table '|| v_user||'.'||v_tabname || ' THESE USERS HAVE ACCESS RIGHTS:');

        FOR c5 IN obj_priv_cur(v_user, v_tabname) LOOP 

	    DBMS_OUTPUT.PUT(c5.grantee);

            DBMS_OUTPUT.PUT('    ');

            DBMS_OUTPUT.PUT_LINE(c5.privilege);    

        END LOOP;  
 

        --------- end   WHO HAS ACCESS TO THIS TABLE -------- 
 
 

        --------  begin TRIGGERS                    ---------

        DBMS_OUTPUT.PUT_LINE(CHR(10)); 

        DBMS_OUTPUT.PUT('-------TRIGGERS ON THIS TABLE   ');

        DBMS_OUTPUT.PUT(v_user||'.'||v_tabname);

        DBMS_OUTPUT.PUT_LINE('--------');

        FOR c5 IN triggers_cur(v_user, v_tabname) LOOP 

	    DBMS_OUTPUT.PUT_LINE('Name: ' ||c5.TRIGGER_NAME);

	    DBMS_OUTPUT.PUT_LINE('The trigger is ' ||c5.STATUS);

	    DBMS_OUTPUT.PUT_LINE(c5.TRIGGER_TYPE);

	    DBMS_OUTPUT.PUT_LINE(c5.TRIGGERING_EVENT);

	    DBMS_OUTPUT.PUT_LINE(c5.COLUMN_NAME);

            DBMS_OUTPUT.PUT_LINE(c5.WHEN_CLAUSE);  

            put_buffer(c5.TRIGGER_BODY);   

        END LOOP;
 

        --------- end   TRIGGERS                        -------- 

  EXCEPTION 

  WHEN no_data_found THEN 

  DBMS_OUTPUT.PUT_LINE('This table: '||v_tabname||', Does not exist or has no constraints!'); 

END an_tab;

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Cannot open form error 6 66
query in Oracle forms Builder 2 41
Oracle -- identify blocking session 24 43
Error executing command from server 6 42
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now