Wednesday 14 September 2011

Query to find all the dependent tables in Oracle

I was trying to find a easy query to get the list of tables that are dependent on a given table. I developed a query, that will give the chain of dependencies.
SELECT ac2.table_name,
  Ac1.Table_Name AS Referenced_Table,
  ac2.u_level
FROM all_constraints ac1,
  (SELECT Constraint_Name,
    Table_Name,
    R_Constraint_Name,
    level AS u_level
  FROM All_Constraints
  WHERE Constraint_Type              = 'R'
    START WITH Table_Name            = :r_table_name
    CONNECT BY Prior Constraint_Name = R_Constraint_Name
  ) Ac2
WHERE Ac1.Constraint_Name = Ac2.R_Constraint_Name
ORDER BY u_level,
  ac2.table_name,
  ac1.table_name