Oracle Primary Key - Foreign Key Report

---

 

 

Primary Key - Foreign Key Report
pk fk Report On user




UNDEF ENTER_OWNER_NAME
COLUMN A_OWNER            FORMAT A15      HEADING 'Primary|Table|Owner'
COLUMN B_OWNER            FORMAT A15      HEADING 'For|Table|Owner'
COLUMN TABLE_NAME         FORMAT A15      HEADING 'Table Name'
COLUMN COLUMN_NAME        FORMAT A15      HEADING 'Column Name'
REM
SET LINES 132 pages 66 feedback off verify off
TTITLE "Primary Key - Foreign Key Report"
REM
WITH reference_view AS
     (SELECT a.owner, a.table_name, a.constraint_name, a.constraint_type,
             a.r_owner, a.r_constraint_name, b.column_name
        FROM dba_constraints a, dba_cons_columns b
       WHERE a.owner LIKE UPPER ('&&ENTER_OWNER_NAME')
         AND a.owner = b.owner
         AND a.constraint_name = b.constraint_name
         AND constraint_type = 'R'),
     constraint_view AS
     (SELECT a.owner a_owner, a.table_name, a.column_name, b.owner b_owner,
             b.constraint_name
        FROM dba_cons_columns a, dba_constraints b
       WHERE a.owner = b.owner
         AND a.constraint_name = b.constraint_name
         AND b.constraint_type = 'P'
         AND a.owner LIKE UPPER ('&&ENTER_OWNER_NAME'))
SELECT CV.a_owner a_owner, CV.table_name, rv.column_name, rv.owner b_owner,
       rv.table_name, rv.column_name
  FROM reference_view rv, constraint_view CV
 WHERE rv.r_constraint_name = CV.constraint_name AND rv.r_owner = CV.b_owner;




Primary Key - Foreign Key Report

Primary                                         For
Table                                           Table
Owner           Table Name      Column Name     Owner           Table Name      Column Name
--------------- --------------- --------------- --------------- --------------- ---------------
SCOTT           ORDERS          ORDER_ID        SCOTT           ORDER_LINES     ORDER_ID