Home » SQL & PL/SQL » SQL & PL/SQL » Urgent: Need to find parent child relationships between table
Urgent: Need to find parent child relationships between table [message #204855] Wed, 22 November 2006 06:55 Go to next message
sumanroyc
Messages: 1
Registered: November 2006
Junior Member
Hi,

We have a requirement of finding out the parent child relationships between the tables of a schema.

For example if A has a foreign key referencing B and B has a foreign key referencing C we should have a list like :-

C
B
A

Please let me know how we can do this through a SQL query.

Cheers !!
Suman
Re: Urgent: Need to find parent child relationships between table [message #204860 is a reply to message #204855] Wed, 22 November 2006 07:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do either of these take your fancy:
select r.table_name  child_table
      ,p.table_name  parent_table
from   user_constraints r
      ,user_constraints p
where r.constraint_type = 'R'
and   p.constraint_type = 'P'
and   r.r_constraint_name = p.constraint_name;


select r.table_name  child_table
      ,(select p.table_name from user_constraints p where p.constraint_name = r.r_constraint_name)  parent_table
      ,sys_connect_by_path(table_name,'/')
from   user_constraints r
where  r.constraint_type = 'R'
connect by prior constraint_name = r_constraint_name;
Previous Topic: trigger - one user to another user
Next Topic: call procedure in function
Goto Forum:
  


Current Time: Fri Dec 02 14:12:54 CST 2016

Total time taken to generate the page: 0.12905 seconds