Re: constraints question

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Thu, 30 Apr 2009 08:19:25 +0200
Message-ID: <cd8f74560904292319r4a39c6dai832983165ffd0ea9_at_mail.gmail.com>

Eugene,
what's about

SELECT lpad(' ',level*3) || level L,

       owner,
       TABLE_NAME,
       CONSTRAINT_NAME,
   CONSTRAINT_TYPE,
       R_CONSTRAINT_NAME

FROM dba_constraints
start with table_name='SALES'
CONNECT BY NOCYCLE -- NOCYCLE does not work in 9i   prior TABLE_NAME=TABLE_NAME (+)
  and PRIOR owner = owner (+)
  and PRIOR constraint_name = r_constraint_name(+);

hth
 Martin

On Wed, Apr 29, 2009 at 23:35, Eugene Pipko <eugene.pipko_at_unionbay.com>wrote:

> Hi all,
>
> I started to think about writing an archiving procedure for one of our
> apps.
>
> The idea is to move/delete records from SALES table and all other tables
> that related to it.
>
> I am using the following query to find all children of the starting table:
>
>
>
> select
>
> parents.owner || '.' || parents.table_name parent_table,
>
> child.owner || '.' || child.table_name child_table,
>
> child.r_constraint_name
>
> from
>
> dba_constraints child,
>
> dba_constraints parents
>
> where
>
> child.r_constraint_name = parents.constraint_name
>
> and child.r_owner = parents.owner
>
> and parents.table_name = 'SALES”;
>
>
>
> This query returns 7 tables that are children to SALES.
>
> So now I have to walk all 7 tables looking for children for them as well
> and so forth…
>
> I can loop through them using PLSQL, but is there a way to find all of them
> all the way down specifying starting table using SQL?
>
>
>
> Thanks,
>
>
>
> Eugene Pipko
>
> Seattle Pacific Industries
>
> office: 253.872.5243
>
> cell: 206.304.7726
>
> P Please consider the environment before printing this e-mail.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

-- 
Martin Berger           martin.a.berger_at_gmail.com
Lederergasse 27/2/14           +43 660 660 83306
1080 Wien                                       http://berx.at/
Sent from Vienna, Austria

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 30 2009 - 01:19:25 CDT