CODEWITHSUNDEEP
sqloracle-databasemetadata
devio
devio

Reputation: 1169

Oracle: get all constraints/columns (primary and foreign keys)

This SQL query :

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 a.owner = 'BRANCH_DEV'
AND a.table_name IN ( 'table1','table2' );

returns the constraints of the owner BRANCH_DEV and the tables/columns names. However, I also need to get the name of the column in the "key table" (original table).

Upvotes: 5

Views: 26091

Answers (1)

collapsar
collapsar

Reputation: 17238

Join the constraint column view to the pk constraint view:

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
     , cc_pk.column_name    r_column_name
  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 cc_pk   on (cc_pk.constraint_name   = c_pk.constraint_name      AND cc_pk.owner         = c_pk.owner            )
 WHERE a.owner = 'BRANCH_DEV'
   AND a.table_name IN ( 'table1','table2' )
     ;

Upvotes: 13

Related Questions