Wednesday, April 13, 2016

How to find all child tables of a table in Oracle??

Sometimes it’s useful to find out all the dependent/child tables for a particular table. The SQL statement below will work for an Oracle database:

SELECT
a.owner,a.table_name, a.constraint_name
FROM
sys.all_constraints a,
(SELECT owner,constraint_name from sys.all_constraints
WHERE
owner = 'OWNER' and
table_name = 'TABLE_NAME' and
constraint_type in ('P','U')
) b
WHERE
a.constraint_type = 'R' and
a.r_constraint_name = b.constraint_name and
a.r_owner = b.owner

Where  OWNER is the owner/schema to which the table belongs andTABLE_NAME is the table to be reported on.

No comments:

Post a Comment