Nagesh V. Anupindi

Friday
Aug 18th
Text size
  • Increase font size
  • Default font size
  • Decrease font size
Home Publications Technology Articles Getting Entity and Attribute Relationships Without ER Software

Getting Entity and Attribute Relationships Without ER Software

E-mail Print PDF
User Rating: / 2
PoorBest 

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.

Source/Text/Comments:

--------------------- 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.

This artcle was published in Oracle's Tip & Code Magazine on October 31, 1997.