Oracle Primary key Foreign Key report

Oracle Primary key Foreign Key report
oracle pk key fk key
Oracle pk fk 
Listing Table Dependencies  ( Foreign Key References )

set feed off

set pagesize 10000
ttitle off
--
ttitle left'primary and foreign key relationsships' skip 2
set feed off
set pagesize 10000

--

column datum new_value datum noprint
column for_owner   format a5  heading 'table|owner'
column pri_tsname  format a10 heading 'tablespace'
column for_table   format a17 heading 'from|foreign|table'
column for_col     format a16 heading 'from|foreign|column'
column pri_table   format a17 heading 'to|primary|table'
column pri_col     format a16 heading 'to|primary|column'

break on for_owner skip 1

select
a.owner           for_owner, 
e.tablespace_name pri_tsname,
a.table_name      for_table,
c.column_name     for_col,
b.table_name      pri_table,
d.column_name     pri_col
from dba_constraints a,
dba_constraints      b,
dba_cons_columns     c,
dba_cons_columns     d,
dba_tables e
where a.owner  in ('SCOTT')   ------- <<<<<<<<<<<<<<
and a.r_constraint_name = b.constraint_name
and a.constraint_type = 'R'
and b.constraint_type = 'P'
and a.r_owner = b.owner
and a.constraint_name = c.constraint_name
and a.owner = c.owner
and a.table_name = c.table_name
and b.constraint_name = d.constraint_name
and b.owner = d.owner
and b.table_name = d.table_name
and b.table_name = e.table_name
order by a.owner,a.table_name;



primary and foreign key relationsships

                 from              from             to                to
table            foreign           foreign          primary           primary
owner tablespace table             column           table             column
----- ---------- ----------------- ---------------- ----------------- ----------------
SCOTT EXAMPLE    ORDER_LINES       ORDER_ID         ORDERS            ID
      USERS      ORDER_LINES       ORDER_ID         ORDERS            ID
      SYSTEM     ORDER_LINES       ORDER_ID         ORDERS            ID



=================================


CLEAR COLUMNS
CLEAR BREAK
SET PAUSE ON
SET FEEDBACK OFF
SET PAGESIZE 20
COLUMN "This Table References Fk" FORMAT A30
COLUMN "This Table Pk" FORMAT A30
BREAK ON "This Table References Fk" SKIP 2

SPOOL TAB_DEPEND.txt
SELECT A.TABLE_NAME "This Table References Fk", DECODE( A.R_OWNER, A.OWNER, B.TABLE_NAME, A.R_OWNER || '.' || B.TABLE_NAME ) "This Table Pk"
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B
WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.CONSTRAINT_TYPE ='R'
AND B.CONSTRAINT_TYPE IN ( 'U', 'P' )
GROUP BY A.TABLE_NAME,DECODE( A.R_OWNER, A.OWNER, B.TABLE_NAME,A.R_OWNER || '.' || B.TABLE_NAME )
ORDER BY 1, 2
/

SQL> connect scott/tiger

Connected.
SQL> ed pkfk1

SQL> @pkfk1

primary and foreign key relationsships

This Table References Fk       This Table Pk
------------------------------ ------------------------------
ORDER_LINES                    ORDERS