ORACLE Query to find child tables

By arunraj

In this section we will see about how to find the child tables for a given parent table.

In many projects where we deal with databases, we would like to find the list of child tables for a parent table. A parent table will have a primary key and one or more child tables will be referencing the parent table using a foreign key. But how will you find the list of child tables referencing the parent table.

If you use oracle client like TOAD tool, we can easily find out the child tables. But is there any other way to find the same? yes we can find the list using oracle meta data tables. The following query will give the list of parent table name, parent column name, child table name and child column name.

SELECT a.table_name, c.column_name, b.table_name AS CHILD_TABLE, d.column_name, b.R_CONSTRAINT_NAME FROM user_constraints a, user_constraints b, user_ind_columns c, user_cons_columns d WHERE a.constraint_type = 'P' AND a.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME AND b.CONSTRAINT_TYPE = 'R' AND a.table_name = c.table_name AND a.constraint_name = c.index_name AND b.CONSTRAINT_NAME = d.constraint_name AND a.table_name = '<TABLE_NAME>'

In the above query substitute the required table name at the end of where clause to find the list of child tables for the given parent table.

Technology: 
Oracle
  • 28420 reads

Search