Follow by Email

Tuesday, September 3, 2013

SQL to get the list of child tables for a given table from oracle

Below is the oracle query to get the list of child tables for a given table "Employee"
 
 
SELECT a.table_name,

       a.column_name,

       a.constraint_name,

       c.owner,

       -- referenced pk

       c.r_owner,

       c_pk.table_name      r_table_name,

       c_pk.constraint_name r_pk

  FROM all_cons_columns a

  JOIN all_constraints c

    ON a.owner = c.owner

   AND a.constraint_name = c.constraint_name

  JOIN all_constraints c_pk

    ON c.r_owner = c_pk.owner

   AND c.r_constraint_name = c_pk.constraint_name

WHERE c.constraint_type = 'R'

   AND c_pk.table_name like '%Employee%'

No comments:

Post a Comment