Home » SQL & PL/SQL » SQL & PL/SQL » How to print all child tables for a parent table?
How to print all child tables for a parent table? [message #261142] Wed, 22 August 2007 00:26 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Could someone help me out with the code to access all the child tables for a specific parent table?

I have written the code, but some entries get duplicated.

select p.table_name ptable, c.table_name ctable
from
all_constraints p, all_constraints c
where p.owner = c.r_owner and
p.constraint_name = c.r_constraint_name.

The above code, prints out all the parent tables in the database that have a child table and the child tables it is assoicated with.

Could you please let me know what is wrong with this code?
Re: How to print all child tables for a parent table? [message #261147 is a reply to message #261142] Wed, 22 August 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Seems fine to me.
If you have duplicates maybe there are several constraints between 2 tables. To be sure add "owner, constraint_name" to your query and see if there are still duplicates.
If you don't want them wirh your initial query, you can add "distinct".

Regards
Michel
Re: How to print all child tables for a parent table? [message #261288 is a reply to message #261142] Wed, 22 August 2007 04:59 Go to previous message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Could someone help me out with the code to access all the child tables for a specific parent table?

The above code, prints out all the parent tables in the database that have a child table and the child tables it is assoicated with.

I think you need to add where cleause if you know the name of the table for which you want to know the child tables.

like below

where p.table_name='<table_name>'

This will restrict the output something like below

ptable CTABLE
p1 c1
p1 c2

like this if you specify p.table_name='P1';

If you need only child table name then take out ptable column form select clause.

If you do not need for a specific table then use ditinct as mentioned by Michel.
Previous Topic: SQL: returning selected characters in a string to a variable
Next Topic: procedure
Goto Forum:
  


Current Time: Thu Dec 08 10:41:37 CST 2016

Total time taken to generate the page: 0.09803 seconds