Display Column with it's corresponding Foreign Key Column

This Time I'll try to make a Process that prints a Data Dictionary, I'll make it part by part. and here I'll getthe Columns with it's corresponding Foreign Key Column, the next script will do it for the table name of the variable :TABLE_NAME. The only problem with this is then you have Primary Keys with two columns, it will repeat the table column.

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,
    -- references Column
    r.table_name R_TABLE, r.column_name R_COLUMN
  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
 JOIN all_cons_columns R ON(c_pk.constraint_name = R.constraint_name )
 WHERE c.constraint_type = 'R'
   AND a.table_name = :TABLE_NAME
Source: [stackoverflow]-List of foreign keys and the tables they reference

Comments

Popular posts from this blog

View PDF File on APEX (Simple approach)

Set Custom APP Icon on APEX

ID Basado en trigger y secuencia para todas las tablas - Oracle