Title: Getting Entity and Attribute Relationships Without ER Software

Author: Nagesh Anupindi, a Senior Consultant for Raymond James Consultant in Denver, CO.

Have you lost your Entity Relationships diagram? Or do you want to see if the table relationships in your database match your
ER diagram? Here is an SQL*Plus script that lets you find out the Child and Parent tables (entities) for a given table, and also
the columns (attributes) that are used in defining the relationship.

------------------------------  Start of Script  --------------------------
set echo off
set verify off
accept xTable prompt 'Enter Table Name: '
TTITLE LEFT 'Child Tables for the table: '&xTABLE
break on TABLE_NAME
SELECT   B.TABLE_NAME, C.COLUMN_NAME, C.POSITION
FROM     USER_CONSTRAINTS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C
WHERE    A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
AND      A.TABLE_NAME = C.TABLE_NAME
AND      A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND      A.TABLE_NAME = UPPER('&xTable')
ORDER BY B.TABLE_NAME, C.POSITION;
TTITLE LEFT 'Parent tables for the table: '&xTable
SELECT   A.TABLE_NAME, C.COLUMN_NAME, C.POSITION
FROM     USER_CONSTRAINTS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C
WHERE    A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
AND      B.TABLE_NAME = C.TABLE_NAME
AND      B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND      B.TABLE_NAME = UPPER('&xTable')
ORDER BY A.TABLE_NAME, C.POSITION;
-------------------------------- End of Script --------------------------
Following is a sample output of this script that I ran against
one of my databases:
SQL> @C:\TOTW2
Enter Table Name: TBE
Child Tables for the table: TBE
TABLE_NAME                     COLUMN_NAME                     POSITION
------------------------------ ------------------------------ ---------
TBE_AMENDMENT                  JOB_ID                                 1
                               TBE_ID                                 2
                               DEPT                                   3
                               BUREAU_OR_OFFICE                       4
                               ORG_CODE_PERSONNEL                     5
TBE_RESOURCE_DOLLARS           JOB_ID                                 1
                               TBE_ID                                 2
                               DEPT                                   3
                               BUREAU_OR_OFFICE                       4
                               ORG_CODE_PERSONNEL                     5
TBE_RESOURCE_SD                JOB_ID                                 1
                               TBE_ID                                 2
                               DEPT                                   3
                               BUREAU_OR_OFFICE                       4
                               ORG_CODE_PERSONNEL                     5
15 rows selected.
Parent tables for the table: TBE
TABLE_NAME                     COLUMN_NAME                     POSITION
------------------------------ ------------------------------ ---------
JOB                            JOB_ID                                 1
--------------------------  End of Sample Output -----------------------
Note: If you have DBA privileges and want to find the relationships
of your tables to the tables in other schemas, all you have
to do is change the table names from USER_* to ALL_* in the above
SELECT statements.
About the Author:  Nagesh Anupindi, Ph.D., is a Senior Consultant
in Raymond James Consulting, Denver, CO.  He leads technical teams
for several projects and helps fight fires with ORACLE
development and DBA work.