get references table name and table column

Question

I want to create query that result like this :

column_name, constraint_name, constraint_type, referenced_table_name, referenced_column_name

but I got a problem when build the referenced_table_name and referenced_column_name

this is my query

SELECT cols.column_name, cons.constraint_name, cons.constraint_type
FROM all_constraints cons,  all_cons_columns cols
WHERE cols.table_name = 'EMPLOYEES'
AND cons.owner = 'HR'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner

it just show column_name, constraint_name, and constraint_type. How do I show the rest?? Thank's for any help.....

DvE · Accepted Answer · 2012-01-02 09:16:41Z

2

Something like this?

select cols1.column_name
, r1.constraint_name
, r1.constraint_type 
, cols2.table_name
, cols2.column_name
from all_constraints r1
,    all_cons_columns cols1
,    all_cons_columns cols2
WHERE r1.constraint_name = cols1.constraint_name
AND   r1.owner = cols1.owner
and   r1.r_owner = cols2.owner(+)
and   r1.r_constraint_name = cols2.constraint_name(+)
AND cols1.table_name = 'EMPLOYEES' 
AND r1.owner = 'HR' 
/

answered Jan 2, 2012 at 9:16

DvE

362 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

get references table name and table column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related