Reverse Engineer - Database Schema

There are lots of tools available in the market to reverse engineer your existing database schema objects and creates the Data Model from it
(Eg. Microsoft Visio, JDeveloper etc).

To do this job, I have prepared a SQL Script; the resultset of this query will tell you the Parent/Child relationships (FK_TABLE, FK_COLUMN referring PK_TABLE, PK_COLUMN). I feel this will be very handy script for DBAs, Application Developers and Database Architect to analyze the Parent-Child relationship for their existing database objects in the schema.

I have executed the below script in SCOTT Schema, Please verify the output.

SQL>column fk_table format a30
SQL>column fk_column format a30
SQL>column pk_table format a30
SQL>column pk_column format a30
SQL>set linesize 140
SQL>
SQL> SELECT p.table_name pk_table, 
  2         p.column_name pk_column,
  3         c.table_name fk_table, 
  4      c.column_name fk_column
  5    FROM (SELECT uc.constraint_name, 
  6                 ucc.table_name, 
  7        ucc.column_name
  8            FROM user_constraints uc, 
  9           user_cons_columns ucc
 10           WHERE uc.constraint_name IN (
 11         SELECT r_constraint_name
 12                 FROM user_constraints
 13                 WHERE constraint_type = 'R')
 14           AND uc.table_name = ucc.table_name
 15           AND uc.constraint_name = ucc.constraint_name) p,
 16         (SELECT uc.r_constraint_name, 
 17           ucc.table_name, 
 18        ucc.column_name
 19            FROM user_constraints uc, 
 20          user_cons_columns ucc
 21           WHERE uc.constraint_name IN (
 22         SELECT constraint_name
 23                 FROM user_constraints
 24                 WHERE constraint_type = 'R')
 25             AND uc.table_name = ucc.table_name
 26             AND uc.constraint_name = ucc.constraint_name) c
 27   WHERE p.constraint_name = c.r_constraint_name;


PK_TABLE  PK_COLUMN  FK_TABLE  FK_COLUMN
--------  ---------  --------  ---------
DEPT      DEPTNO     EMP       DEPTNO

Hope this will be useful for you.