Re: SQL to view Table Relationships?

From: Michel Cadot <micadot_at_altern.org>
Date: Tue, 27 Jul 1999 11:19:39 +0200
Message-ID: <7njtld$k6g$1@oceanite.cybercable.fr>

Scott Murray a écrit dans le message <01bed459$e755f2a0$0a00a8c0_at_bammer>...
>I am stumped trying to display the relationships between tables (i.e.,
>which tables have foreign keys to which tables).
>
>I've tried using:
>
>select * from all_tab_columns
>where owner = 'new_demo' [that's my user name]
>;
>
>and some similar commands, but I keep getting no records found.
>
>Can anyone help me out?
>
>Thanks in advance,
>
>Scott Murray
>smurray_at_officecomp.com

Hello Scott,

Here a script that shows the dependence tree of your tables. Because of the use of the "connect by" clause we have to create a temporary table with the data of the user_constraints view.

This script is available only if the foreign key tree is a real tree and not a graph (that is there is no loop, no reflexive relation...) otherwise Oracle return an ORA-01436 (connect by loop in user data).

Set Feedback off

Drop table tmp_disfk;
Create table tmp_disfk as
select a.table_name, a.constraint_name unq_name,

       b.table_name r_table_name, b.constraint_name r_constraint_name

from user_constraints b, user_constraints a
where b.r_owner = a.owner
  and b.r_constraint_name = a.constraint_name

  and b.constraint_type = 'R'
  and a.constraint_type in ('P', 'U')
union
select null, null, a.table_name, null
from user_constraints a
where exists ( select 1 from user_constraints b

               where b.r_owner = a.owner
                 and b.r_constraint_name = a.constraint_name
                 and b.constraint_type = 'R'
                 and a.constraint_type in ('P', 'U') )
  and not exists ( select 1 from user_constraints b
                   where b.table_name = a.table_name
                     and b.constraint_type = 'R' )

;

Column line_ format a80 heading "Foreign Key Tree"

Spool dispFK

Select lpad(' ',3*(level-1))||r_table_name||

       decode(table_name, null, '',
                          ' ('||r_constraint_name||
                             ' references '||unq_name||')' )
       line_

from tmp_disfk a
connect by table_name = prior r_table_name start with table_name is null
/

Spool Off

Drop table tmp_disfk;
Set Feedback On

Hope this will help you. Received on Tue Jul 27 1999 - 04:19:39 CDT