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:\TOTW2Enter 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.
| < Prev | Next > |
|---|