Foreign Key List

balachandar · Jul 14, 2005

Hi,
I would like to get the list of foreign keys in a table and present the information in the following format.

Foreign Key Foreign Key References Referred
Constraint Name Column Name Table Name Column
Name

How I can generate the above report....
I understand there are documentation tools for Oracle?. Are there any freewares or open sources tools
Is there any query that can give me the above list ?

Any tips are welcome

Thanks and Regards
Balachandar Ganesan.

SantaMufasa · Jul 14, 2005

Balachandar,

Your preferred format is a bit difficult to understand given the column mis-alignments. Rather than try to figure it out and rather than write new code for old code that I already have written, I'm posting for you my "Super Describe" script that shows all of the categorical information you listed, above, plus it describes all columns and constraints for a table. ("PK" - Primary Key, "UK" - Unique, "FK" - Foreign Key, "CK" - Check constrain including "Not Null" enforcement)

Section 1 -- Sample invocation and output (that describes Oracle Education's "S_EMP" table):

Code:

SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER(7)     [CK:S_EMP_ID_NN:ENABLED] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:ENABLED] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:ENABLED] "LAST_NAME"
                                     IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:ENABLED] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER(7)     [FK:S_EMP_MANAGER_ID_FK:ENABLED] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:ENABLED] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER(7)     [FK:S_EMP_DEPT_ID_FK:ENABLED] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER(11,2)

 11 COMMISSION_PCT     NUMBER(4,2)   [CK:S_EMP_COMMISSION_PCT_CK:ENABLED]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Another table (Y/N) ?

Section 2 -- One-time-only script (save as "StruMake.sql") that CREATEs a utility table and a procedure that the "always-run" script needs:

Code:

REM **********************************************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages-LLC makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **********************************************************************************************
drop table temp_cons;
create table temp_cons as
select constraint_name, constraint_type, ' ' search_condition,
r_constraint_name, status
from user_constraints
where 1 = 2;
alter table temp_cons modify search_condition varchar(2000)
/
create or replace procedure prep_user_constraints (table_2_do in varchar)
  is
  cursor cons is
     select constraint_name,constraint_type,
            search_condition,r_constraint_name, status
            from user_constraints
            where table_name = upper(table_2_do);
  my_search_condition varchar(32767);
begin
  delete temp_cons;
  commit;
  for i in cons loop
     my_search_condition := i.search_condition;
     insert into temp_cons values
       (i.constraint_name,i.constraint_type,
        my_search_condition,i.r_constraint_name,i.status);
     commit;
  end loop;
end;

Section 3 -- Code that displays structure and constraints (Save as "Stru.sql"):

Code:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any
REM industrial application or purpose nor is there any claim that
REM this or any similarly-distributed scripts are error free or
REM should be used for any purpose other than illustration.
REM **************************************************************
set echo off
set verify off
set feedback off
set linesize 80
set pagesize 23
break on "#" on a on b
accept x prompt "Enter table name: "
col a format a18 heading "Column|Name" word_wrapped
col b format a13 heading "Data Type|and Length"
col c format a43 heading "[Constraint Type: Name: En-/Dis-abled]|and Enforcement" word_wrapped
col "#" format 99 heading "Col| # "
exec prep_user_constraints('&x')
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
       '[' ||
           decode(con.constraint_type,'P','PK','R','FK','C','CK','U','UK')
           || ':' ||con.constraint_name
           || ':' ||con.status
           || '] ' ||
           decode(con.constraint_type,
              'P','NOT NULL/UNIQUE',
              'U','UNIQUE',
              'C',con.search_condition,
              'R','Match : '||r_constraint_name) c
from   temp_cons con,
       user_cons_columns con_col,
       user_tab_columns col
where  col.table_name      = upper('&x')
  and  con.constraint_name = con_col.constraint_name
  and  col.table_name      = con_col.table_name
  and  col.column_name     = con_col.column_name
  )
union
((select column_id "#",
         col.column_name a,
         col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col
 where col.table_name = upper('&x'))
minus
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col, user_cons_columns con_col
 where col.table_name  = upper('&x')
   and col.table_name  = con_col.table_name
   and col.column_name = con_col.column_name)
)
order by 1
/
accept again prompt "Another table (Y/N) ? "
set heading off
set pagesize 0
spool temp.sql
set termout off
select decode(upper('&again'),'Y','@stru') from dual;
set termout on
spool off
set heading on
set pagesize 23
set feedback on
@temp

Let us know if this offers insight for your need.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

balachandar · Jul 14, 2005

Hi,
Thanks for the message. I have found a way to do it....

SELECT

USER_CONSTRAINTS.CONSTRAINT_NAME AS NAME,
'Referential Integrity',
USER_CONS_COLUMNS.COLUMN_NAME || ' References ' || X.TABLE_NAME || '.' || Y.COLUMN_NAME

FROM
USER_CONSTRAINTS,
USER_CONS_COLUMNS,
USER_CONSTRAINTS X,
USER_CONS_COLUMNS Y

WHERE
USER_CONSTRAINTS.TABLE_NAME= ****enter the table name here*************
AND
USER_CONS_COLUMNS.CONSTRAINT_NAME = USER_CONSTRAINTS.CONSTRAINT_NAME AND
USER_CONSTRAINTS.CONSTRAINT_TYPE IN ('R') AND
X.CONSTRAINT_NAME = Y.CONSTRAINT_NAME
AND X.CONSTRAINT_NAME =USER_CONSTRAINTS.R_CONSTRAINT_NAME

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Foreign Key List

balachandar

Programmer

SantaMufasa

Technical User

balachandar

Programmer

Similar threads

Part and Inventory Search

Sponsor