Re: Table Information Help

From: Stephen Bell <stephen.bell_at_cgi.ca>
Date: Wed, 15 Aug 2001 09:10:21 -0400
Message-ID: <evue7.21545$KG2.2365760@news20.bellglobal.com>

Hi,

There are a couple of very extensive threads on this....one of which I'll paste here for you, but there was quite a lengthy discussion on it just within the last couple of days...you can search the archives on this subject to get the full discussion..to save
you some time, I'll paste the solution proposed by Daniel Morgan (thanks Daniel...this one's a keeper !)

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 dba_constraints T, dba_constraints R

WHERE T.r_constraint_name = R.constraint_name
  AND T.r_owner = R.owner
  AND T.constraint_type='R'
  AND R.owner = 'OMS2'
  AND T.owner = 'OMS2';

Change the owner from 'OMS2' to whatever you want.....this script works great ! Thanks again Daniel

Steve
"Twin" <tweeting100_at_hotmail.com> wrote in message news:997875511.347046_at_ernani.logica.co.uk...
> Hi,
>
> How do I retrieve information about tables within Oracle instead of
looking
> at the build script? For example:
>
> If I wanted to know the foreign keys of a particular table and where they
> are pointing to, how do I go about doing this ? I'm sick of having to use
> another terminal window to view the various build scripts whenever I want
> this information - surely there's an easier way within Oracle itself ?
>
> Thanks in advance.
>
>
Received on Wed Aug 15 2001 - 08:10:21 CDT