Re: Is There Any SQL*Plus Command To Display Table Relationships?

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Sun, 03 Feb 2002 20:56:22 +0100
Message-ID: <3C5D95E6.D941E9C5_at_skynet.be>

aleatory wrote:
>
> Hi,
>
> I'm looking for SQL*Plus command to display table
> relationships. (Primary-Foreign keys, one-to-one,
> one-to-many, many-to-many, etc.)
>
> I usually have no problem when I deal with two or
> three tables; however, when I have to deal with more
> than four or five tables, I very often produce
> cartesian products.
>
> Desperately need help on joining tables...
>
> Thanks in advance!
>
> alea

Hallo Alea,

this is what I use, maybe it answers your question:

select substr(c.table_name,1,24) child,
       substr(c.constraint_name,1,15) constraint,
       substr(cc.column_name,1,15) column_name,
       substr(p.table_name,1,22) parent

 from user_cons_columns cc, user_constraints c, user_constraints p

 where c.table_name = upper('&&tablename')
 and   c.constraint_type = 'R'
 and   p.owner           = c.r_owner
 and   p.constraint_name = c.r_constraint_name
 and   cc.owner           = c.owner

 and cc.constraint_name = c.constraint_name union

select substr(c.table_name,1,24) child,
       substr(c.constraint_name,1,15) constraint,
       substr(cc.column_name,1,15) column_name,
       substr(p.table_name,1,22) parent

 from user_cons_columns cc, user_constraints c, user_constraints p

 where p.table_name = upper('&&tablename')
 and   p.constraint_type in ('P','U')
 and   p.owner           = c.r_owner
 and   p.constraint_name = c.r_constraint_name
 and   cc.owner           = c.owner

 and cc.constraint_name = c.constraint_name ;

-- 

Gerard H. Pille

(The Beatles Reunion is neigh!)

Received on Sun Feb 03 2002 - 20:56:22 CET