constraints question

From: Eugene Pipko <>
Date: Wed, 29 Apr 2009 14:35:55 -0700
Message-ID: <>

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:


              parents.owner || '.' || parents.table_name  parent_table,
              child.owner || '.' || child.table_name child_table,
             dba_constraints child,
             dba_constraints parents
              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?


Eugene Pipko
Seattle Pacific Industries
office: 253.872.5243
cell: 206.304.7726
P Please consider the environment before printing this e-mail.

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

Original text of this message