Finding all the child tables of a table in Oracle

When doing schema changes there are often times when I want to know all the child tables of the current table. Also when doing a delete and I want to find child tables to make sure the data is cleaned up correctly.

The SQL to do this is pretty easy.

select table_name, constraint_name 

from all_constraints

where constraint_type = 'R'

and r_constraint_name in

    (select constraint_name

    from all_constraints

    where upper(table_name) = upper(:OBJECT_NAME)

    and constraint_type IN ('P', 'U'))

    order by table_name

It would be nice if some of the IDEs for Oracle development had this as standard information about a table.

I created an XML extension for Oracle SQL Developer to give this information as a new tab. Originally posted here.

It is reproduced below as that link needs a login to get to.

Following on from Sue Harper's blog entry about adding your own tabs to SQL Developer this example adds a tab to display all child tables that have a foreign key pointing to the Primary Key or a Unique Key in the context table.

Following the directions on Sue's blog about how to create your own tab http://sueharper.blogspot.com/2007/04/how-to-add-tabs-to-sql-developer.html you can create your own tabs as required.

I wanted a tab that would show me all the child tables of the current table.

The structure of the XML file needed for this is as below

<items>

<item type="editor" node="TableNode" vertical="true">

<title><![CDATA[Child Tables]]></title>

<query>

<sql>

<![CDATA[

select  table_name,

constraint_name 

from all_constraints

where  constraint_type = 'R'

and r_constraint_name in

(select constraint_name

from all_constraints

where upper(table_name) = upper(:OBJECT_NAME)

and constraint_type IN ('P', 'U'))

order by table_name]]>

</sql>

</query>

</item>

</items>

Might be an enhancement I can submit to Quest for SQL Navigator.