vineri, 16 ianuarie 2015

Find the tables and columns referenced by a given column from a given table

If you are wondering about some details concerning an entity used in a table by its id, can be frustrating to search for the referenced table. 
e.g.: you query the wages table, where all the employees are referred only by their id and don't know the name of the employee having the id=4356...

The solution is:

SELECT a.table_name, a.column_name, a.constraint_name, c.owner,
       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 lower(a.table_name) = 'table_name' and lower(column_name)='column_name' and lower(a.owner)='your_schema';