OraFAQ Forum: SQL & PL/SQL » All Child tables of given table

Home » SQL & PL/SQL » SQL & PL/SQL » All Child tables of given table (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

All Child tables of given table [message #619955]

Sat, 26 July 2014 10:43

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All ,

Could any body please let us know how to find out all multi-level child tables given tables by using single query .

Ex Table1 have 2 child tables
They area table2A , table2B

Table2a have two child tables . Table3a1 , table3a2.
Table2B have two child tables . Table2b1 , table2b2.

Like this need to get the possible child tables of all tables by using single query.

Thanks
SaiPradyumn

Report message to a moderator

Re: All Child tables of given table [message #619958 is a reply to message #619955]

Sat, 26 July 2014 11:08

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

For your benifit of learning, first tell us whether you know how DB objects have dependencies and how they reference each other? If yes, then post the table description including the dependencies. Based on that you can query the dictionary views to get your desired output.

Report message to a moderator

Re: All Child tables of given table [message #619962 is a reply to message #619958]

Sat, 26 July 2014 11:27

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Yes Lalith ,

I know that by using data dictionary views( user_constraints , user_cons_columns), columns (constraint_name , r_constraint_name)
we can find out the first level child tables of given table.

But here my question is how to find out child tables of first level child table & and it has to continue like that to find out all possible child tables .

Thanks
SaiPradyumn

Report message to a moderator

Re: All Child tables of given table [message #619963 is a reply to message #619955]

Sat, 26 July 2014 11:29

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ALL_CONTRAINTS gives you the referential integrity constraints and primary keys, you can use hierarchical query (CONNECT BY) to go from a table -> its PK -> all FK referencing this PK -> the tables -> their PK -> ...

Report message to a moderator

Previous Topic:	Hierarchical query, rolling up value from child to parent
Next Topic:	Wrapper Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 17:51:58 CDT 2024