Re: Making an ERD from an existing db

From: Tim Shute <Tshute_at_nisoft.co.uk>
Date: Wed, 4 Nov 1998 14:07:03 +0000
Message-ID: <qcKJnGAH+FQ2IAco@nisoft.demon.co.uk>

In article <robertdg-2910981126310001_at_robertsd-mac.hpw.pri.bms.com>, Dan Roberts <robertdg_at_bms.com> writes
>Hello All
>
>
>I have an existing Oracle rdbms for which I would like to draw up an ERD.
>How can I get PK and FK information from the DB in the process of
>determining the realtionships btw the tables as I do a desc on each
>table??
>
>Thanks!!.Dan
>

Here is the text of a useful SQL snippet that I found buried deep within on of the Oracle support notes CDs that accompanied 7.3.4. --------------cut-----------
-- This will generate all schema information for the current user SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET TIMING OFF
SET PAUSE OFF
SET PAGESIZE 0
SET LINESIZE 255   SPOOL tmp.sql
SELECT 'SELECT RPAD('''||table_name||''',30),RPAD('''||

       tablespace_name||''',24), ' || 
       'TO_CHAR(COUNT(*),''9,999,999,999'') FROM ' || table_name || ';' 

FROM user_tables
ORDER BY table_name;
SPOOL OFF
SET TERMOUT ON   SET LINESIZE 80   SPOOL schema.lst  

PROMPT
PROMPT
SELECT 'SCHEMA QUICK-LIST FOR USERID ' || user ||

       ' AS OF ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI') FROM DUAL;

PROMPT =================================================================

=  

PROMPT
PROMPT
PROMPT Tables Overview
PROMPT ===============
PROMPT

PROMPT Table                          Tablespace          Number of Rows 
PROMPT ------------------------------ ------------------------ ---------

-----|
@tmp
HOST rm tmp.sql  

PROMPT
PROMPT
PROMPT Referential Constraints

PROMPT ======================= 
PROMPT 
PROMPT Table Constraint               T Referenced  Constraint    D 
PROMPT ------------------------ ------------------------ ---------------

------------|
BREAK ON "Table" SKIP 1
SELECT RPAD(table_name, 24) "Table",

       RPAD(constraint_name, 24) "Constraint", 
       constraint_type "T", 
       RPAD(r_constraint_name, 24) "Referenced Constraint", 
       RPAD(delete_rule, 1) "D" 

FROM user_constraints
WHERE constraint_type IN ('P', 'R', 'U') ORDER BY table_name, constraint_type;  

PROMPT
PROMPT
PROMPT Tables and Columns

PROMPT ================== 
PROMPT 
PROMPT Table                    Column                   T Len  Pre Sca

N

PROMPT ------------------------ ------------------------ - ---- --- ---

-|
BREAK ON "Table" SKIP 1
SELECT RPAD(table_name, 24) "Table",

       RPAD(column_name, 24) "Column", 
       DECODE(data_type, 
          'CHAR', 'C', 
          'VARCHAR', 'V', 
          'VARCHAR2', 'V', 
          'NUMBER', 'N', 
          'DATE', 'D', 
      '?') "T", 
       TO_CHAR(data_length,'999') "Len", 
       TO_CHAR(data_precision,'99') "Pr", 
       TO_CHAR(data_scale,'99') "Sc", 
       nullable "N" 

FROM user_tab_columns
ORDER BY table_name, column_id;  

PROMPT
PROMPT
PROMPT Non-Constraint Indexes

PROMPT ====================== 
PROMPT 
PROMPT Table     Index                    U Column 
PROMPT ------------------------ ------------------------ -  
------------------------| 

BREAK ON "Table" SKIP 1 ON "Index"
SELECT RPAD(i.table_name, 24) "Table",

       RPAD(i.index_name, 24) "Index", 
     RPAD(i.uniqueness,1) U, 
       RPAD(c.column_name,24) "Column" 

FROM user_indexes i, user_ind_columns c WHERE i.table_name = c.table_name
AND i.index_name = c.index_name
ORDER BY i.table_name, i.index_name, c.column_position;  

SPOOL OFF   --------------cut-----------

Not very pretty, but it contains all that you might need.

Dare I mention Designer 2000 and reverse engineering?

HTH
--
Tim Shute
Software Engineering Manager

NiSoft (UK) Limited
Unit 8, 31 Ballynahinch Road, Carryduff, BELFAST BT8 8EH Tel: +44 (0) 1232 814121, Fax: +44 (0) 1232 813962 email: tshute_at_nisoft.co.uk
Internet: www.nisoft.co.uk Received on Wed Nov 04 1998 - 08:07:03 CST