Re: constraints question
- From: Jack van Zanen <jack@xxxxxxxxxxxx>
- To: Mark.Bobak@xxxxxxxxxxxx
- Date: Thu, 30 Apr 2009 14:36:30 +1000
try this
select lpad(' ',2*(level-1)) ||table_name from
(
select a.table_name , b.table_name parent
from user_constraints a left outer join user_constraints b on (a.
r_constraint_name=b.constraint_name)
)
start with table_name='SALES'
connect by prior table_name=parent
brgds
Jack
2009/4/30 Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>
> 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… J
>
>
>
> 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.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
--
Jack van Zanen
-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation
Other related posts:
