Tuesday, November 15, 2011

How to get parent/child of a table in Oracle


This query gives list of children tables of a given table:


SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('&tab');


And this one gives list of parent tables of a given table:

SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('&tab');

1 comments:

Prabhu Shankar said...

Thanks and great work

Post a Comment