RE: constraints question

I think you should be able to do it by walking DBA_CONSTRAINTS w/ a start 
with/connect by.....but it's late and I'm almost out the door... :)

If no one else replies, I'll take a crack at it tomorrow....

-Mark

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Eugene Pipko
Sent: Wednesday, April 29, 2009 5:36 PM
To: 'oracle-l-freelists'
Subject: constraints question

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.

Other related posts: