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

[Quoted] Once again a foreign key based solution. This is no way to write a WHERE clause.

To write a WHERE clause you must do real work and understand the data.

Daniel Morgan

"Gerard H. Pille" wrote:

> 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 - 15:45:25 CET