Re: Tool for table relation

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Thu, 08 May 2003 08:04:49 -0700
Message-ID: <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>;

--
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 Thu May 08 2003 - 17:04:49 CEST