constraints question

From: Eugene Pipko <eugene.pipko_at_unionbay.com>
Date: Wed, 29 Apr 2009 14:35:55 -0700
Message-ID: <34DB87F47199374280ADFD2968CDBCFA87D621FC2D_at_MAIL01KT.seattlepacificindustries.com>

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.

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

Received on Wed Apr 29 2009 - 16:35:55 CDT