Re: Tool for table relation

From: Gerard H. Pille <geert.pille_at_vandemoortele.com>
Date: 9 May 2003 02:20:50 -0700
Message-ID: <41b04c0d.0305090120.22bb4d72_at_posting.google.com>

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EBA7211.25FD3BE7_at_exxesolutions.com>...
> "Gerard H. Pille" wrote:
>
> > Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EB91C21.8E066D7_at_exxesolutions.com>...
> > > Virtual Freak wrote:
> > >
> > > > Hi, is there a tool that can show me all of the tables relations to each
> > > > other on a 8I standard DB?
> > > >
> > > > Eric Pearson
> > > > virtual.freak_at_sympatico.ca
> > >
> > > ERWin, Designer, Visio
> >
> > And the somewhat less graphical:
> >
> > select * from all_constraints where constraint_type = 'R';
>
> And excellent suggestion though I would suggest a slightly more complex variation on your query:
>
> SELECT T.owner AS CHILD_OWNER,
> T.table_name AS CHILD_TABLE,
> T.constraint_name AS FOREIGN_KEY_NAME,
> R.owner AS PARENT_OWNER,
> R.table_name AS PARENT_TABLE,
> R.constraint_name AS PARENT_CONSTRAINT
> FROM all_constraints T, all_constraints R
> WHERE T.r_constraint_name = R.constraint_name
> AND T.r_owner = R.owner
> AND T.constraint_type='R'
> AND R.owner = <schema_name>
> AND T.owner = <schema_name>;

Hallo Daniel,

[Quoted] You must have thought I was a masochist, digesting raw data from all_constraints, but not so, I just wanted the OP to use his own "imagination".

[Quoted] Here is what I use:

[Quoted] select decode(c.status,'ENABLED','C','c') t,
       SUBSTR(c.constraint_name,1,31) relation,
       substr(cc.column_name,1,24) columnname,
       substr(p.table_name,1,20) tablename
 from all_cons_columns cc, all_constraints p, all_constraints c
 where c.owner           = upper('&&schema')
 and   c.table_name      = upper('&&tabname')
 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
 and   cc.table_name      = c.table_name

union all

select decode(c.status,'ENABLED','P','p') t,
       SUBSTR(c.constraint_name,1,31) relation,
       substr(cc.column_name,1,24) columnname,
       substr(c.table_name,1,20) tablename
 from all_cons_columns cc, all_constraints p, all_constraints c
 where p.owner           = upper('&&schema')
 and   p.table_name      = upper('&&tabname')
 and   p.constraint_type in ('P','U')
 and   c.r_owner           = p.owner

 and c.r_constraint_name = p.constraint_name  and c.constraint_type = 'R'

 and   cc.owner           = c.owner
 and   cc.constraint_name = c.constraint_name
 and   cc.table_name      = c.table_name

ORDER BY 1, 4, 2, 3
/ Received on Fri May 09 2003 - 11:20:50 CEST