Re: Tool for table relation
Date: Fri, 09 May 2003 06:30:26 -0700
Message-ID: <3EBBAD72.5FED2029_at_exxesolutions.com>
"Gerard H. Pille" wrote:
> 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,
>
> 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".
>
> Here is what I use:
>> union all
> 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
> 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
> /
Nice.
But no I didn't think you were a masochist. I thought you were a sadist. ;-)
-- Daniel Morgan http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp damorgan_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Fri May 09 2003 - 15:30:26 CEST