Solved

Script to Determine PK\FK Related Tables

Posted on 2009-05-12
4
1,161 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 10g - insert string with special characters 8 58
SQL query question 8 78
SQL Developer 6 49
PL SQL Developer 7 37
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

803 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