Solved

Script to Determine PK\FK Related Tables

Posted on 2009-05-12
4
1,172 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 74

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 74

Expert Comment

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

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

688 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