Re: constraints question
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: eugene.pipko@xxxxxxxxxxxx
- Date: Thu, 30 Apr 2009 08:19:25 +0200
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@xxxxxxxxxxxx>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@xxxxxxxxx
Lederergasse 27/2/14 +43 660 660 83306
1080 Wien http://berx.at/
Sent from Vienna, Austria
Other related posts:
