Solved

Script to Determine PK\FK Related Tables

Posted on 2009-05-12
4
1,154 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
Comment Utility
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
Comment Utility
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
Comment Utility
need anything else?
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 200 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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

12 Experts available now in Live!

Get 1:1 Help Now