Abstract
Script for listing the Primary and Foreign Key Relationships in a database.
 

Product Name, Product Version

 Oracle Server, 7.3.x to 9.x
Platform Platform Independent
Date Created 21-Jun-2000
 
Instructions 
Execution Environment:
     <SQL, SQL*Plus, iSQL*Plus>

Access Privileges:
     Requires DBA access privileges to be executed.

Usage:
     sqlplus <user>/<pw> @[SCRIPTFILE] 


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.
 
Description 
The following script generates a report of the primary and
foreign key relationships of tables within the database.

Sample Output
=============

FOR_OWNER  FOR_TABLE  FOR_COL         PRI_OWNER  PRI_TABLE  PRI_COL
---------- ---------- --------------- ---------- ---------- ---------------
TSMASTER   VALIDATION VALIDATION_TYPE TSMASTER   VALIDATION VALIDATION_TYPE
           S                                     _TYPES

TSMASTER   VALIDATION DISCREPANCY_MES TSMASTER   DISCREPANC DISCREPANCY_MES
           S          SAGE_ID                    Y_MESSAGES SAGE_ID

TSMASTER   VM_PROTCL_ PROTCL_ID       TSMASTER   PROTOCOLS  PROTCL_ID
           SPECIFIC_D
           RVTNS

TSMASTER   VM_VIEW_CO VIEW_ID         TSMASTER   VM_VIEW_NA VIEW_ID

FOR_OWNER  FOR_TABLE  FOR_COL         PRI_OWNER  PRI_TABLE  PRI_COL
---------- ---------- --------------- ---------- ---------- ---------------
           LUMNS                                 MES

TSMASTER   VM_VIEW_CO COLUMN_ID       TSMASTER   VM_COLUMN_ COLUMN_ID
           LUMNS                                 NAMES

TSMASTER   WORD_CODES PRODUCT_NM      TSMASTER   ACTIVE_ING PRODUCT_NM
                                                 REDIENTS
 
References 
 
 
Script 
REM SCOPE & APPLICATION
REM
REM This script can only be run by users with DBA role or can be
REM changed slightly to allow all users to run it.
REM
REM
REM Locating the Primary & Foreign Key Relationships
REM ------------------------------------------------
REM
REM The following query will give primary and foreign key relationships for
REM tables on a database level.  This script can only be run by users with
REM DBA role or explicitly granted the dba_constraints and dba_cons_columns
REM views.
REM
REM You can change the script slightly to allow all users to run it, by
REM simply changing the view names from 'dba_' to 'user_'


column for_owner format a10
column for_table format a10
column pri_owner format a10
column pri_table format a10
column for_col format a15
column pri_col format a15

select  a.owner for_owner, a.table_name for_table, c.column_name for_col,
b.owner pri_owner, 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
where  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    b.constraint_name=d.constraint_name
and    a.owner = c.owner
and    a.table_name=c.table_name
and    b.owner = d.owner
and    b.table_name=d.table_name
/