Home » SQL & PL/SQL » SQL & PL/SQL » Get child tables.
Get child tables. [message #36700] Thu, 13 December 2001 08:48 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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


----------------------------------------------------------------------
Previous Topic: Sql Statements
Next Topic: How to spell the numbers
Goto Forum:
  


Current Time: Mon Feb 24 15:43:47 CST 2020