Get child tables. [message #36700] |
Thu, 13 December 2001 08:48 |
Srinivas
Messages: 138 Registered: June 2000
|
Senior Member |
|
|
Is anybody has a script to get all the child tables of the master table, Like PARENT, CHILD,GRAND CHILD,GRAND GRAND CHILD etc.
My driving table is TABLE_A and TABLE_A has TABLE_B and TABLE_C. TABLE_B has childs TABLE_B1,TABLE_B2 and TABLE_C has TABLE_C1, TABLE_C2, etc..
so when I request for TABLE_A, I want to get the Foreign constraints on the tables and the child table names for all TABLE_A,TABLE_B, TABLE_A1,TABLE_A2,TABLE_B1,TABLE_B2 etc..
Please post the sql.
----------------------------------------------------------------------
|
|
|
Re: Get child tables. [message #36702 is a reply to message #36700] |
Thu, 13 December 2001 09:59 |
Susan
Messages: 102 Registered: October 2001
|
Senior Member |
|
|
this should get you parents and children
select b.table_name Child_table,a.table_name parent_table ,b.constraint_name,b.r_constraint_name
from user_constraints b,user_constraints a
where a.constraint_type='P' And
a.constraint_name=b.r_constraint_name
order by b.table_name;
----------------------------------------------------------------------
|
|
|
Re: Get child tables. [message #36706 is a reply to message #36702] |
Thu, 13 December 2001 11:59 |
Srinivas
Messages: 138 Registered: June 2000
|
Senior Member |
|
|
That query will give for all the tables.
I want child tables for a driver table.
When I give PARENT table, it should list all the CHILDS, GRAND CHILDS, GRAND GRAND CHILD etc...
How can I get this.
----------------------------------------------------------------------
|
|
|
Re: Get child tables. [message #36735 is a reply to message #36702] |
Sat, 15 December 2001 05:04 |
Susan
Messages: 102 Registered: October 2001
|
Senior Member |
|
|
add another where clause i.e. where a.table_name = 'MY_TABLE'
----------------------------------------------------------------------
|
|
|
Re: Get child tables. [message #36737 is a reply to message #36702] |
Sat, 15 December 2001 11:56 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
create procedure with code below and call that procedure using
SQL> exec parent_child(table_name);
create or replace procedure parent_child(tname varchar2,p_chr varchar2 default '*') IS
l_child varchar2(255):='';
l_chr varchar2(255):='';
l_tname varchar2(255):='';
begin
if p_chr='*' THEN
dbms_output.put_line('Parent table'||chr(9)||chr(9)||'Child table');
end if;
for crec in (select * from user_constraints where table_name=tname
and constraint_type='P') loop
l_tname:=tname;
for crec1 in
(select * from user_constraints
where r_constraint_name=crec.constraint_name)
loop
parent_child(crec1.table_name,'$');
IF length(l_child)>=1 THEN
l_child:=l_child||','||crec1.table_name;
ELSE
l_child:=crec1.table_name;
END IF;
end loop;
IF length(l_child)>=1 THEN
dbms_output.put_line(l_tname||chr(9)||chr(9)||chr(9)||l_child);
END IF;
end loop;
END;
Suresh Vemulapalli
----------------------------------------------------------------------
|
|
|