Saturday, April 19, 2008

Foreign Key query

Show FK for a column:

SELECT con.name constraint_name
,bt.name parent_table
,bc.name parent_column
,tab.name child_table
,col.name child_column
,decode(i2.refact,1,'YES','NO') cascade_delete
FROM sys.con$ con ,sys.obj$ tab ,sys.col$ col ,sys.ccol$ i ,sys.obj$ bt ,sys.col$ bc ,sys.cdef$ i2
WHERE con.con# = i.con#
AND tab.obj# = i.obj#
AND col.obj# = i.obj#
AND col.col# = i.col#
AND i2.con# = i.con#
AND bt.obj# = i2.robj#
AND bc.obj# = i2.robj#
AND bc.col# = i2.cols
AND i2.type# = 4
AND con.name = 'COLNAME????'

I will show some output tomorrow when i install 11g DB.

No comments: