0

How can I do to (left)join user_tab_columns with user_cons_columns and user_constraints to get the result below:

Table, Column, SourceTable, SourceColumn
ORDER, ORDER_ID, null, null
ORDER, ORDER_DATE, null, null
ORDER, ORDER_CLIENT, CLIENT, CLIENT_ID
CLIENT, CLIENT_ID, null, null
ORDER_DETAIL, ORDER_ID, ORDER, ORDER_ID
ORDER_DETAIL, PRODUCT_ID, PRODUCT, PRODUCT_ID
PRODUCT PRODUCT_ID, null, null

So... I need all table and columns but once a column is a foreign key, I need to know the table and the column (primary key) related.

Theodoro Schier
  • 35
  • 5
  • Seems pretty close to: http://stackoverflow.com/questions/1729996/list-of-foreign-keys-and-the-tables-they-reference – xQbert May 02 '17 at 19:25

2 Answers2

1

Slight edits to linked answer:

List of foreign keys and the tables they reference all_ to user sources.

Course you could just use all and then filter on owner.

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 user_cons_columns a
  LEFT JOIN user_constraints c 
    ON a.owner = c.owner
   AND a.constraint_name = c.constraint_name
  LEFT JOIN user_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 a.table_name = :TableName
Community
  • 1
  • 1
xQbert
  • 34,733
  • 2
  • 41
  • 62
0

check following query : select a.table_name,c1.column_name,b.table_name,c2.column_name from USER_constraints a,USER_constraints b,user_cons_columns c1,user_cons_columns c2 where a.r_constraint_name = b.constraint_name and a.table_name = c1.table_name and b.table_name = c2.table_name if you just need FK constraint use a.constraint_type ='R' in where clouse.

Shahram
  • 56
  • 2