View Single Post
  #3 (permalink)  
Old 09-22-2006, 05:04 AM
charlesryoung charlesryoung is offline
Junior Member
 
Join Date: Sep 2006
Posts: 3
charlesryoung is on a distinguished road
Default Oracle answer. Re: How do I find out which columns are foreign keys in a table?

Perhaps these two queries will help:
[sql]
select Parent.TABLE_NAME Parent_TABLE,
Parent.CONSTRAINT_NAME Parent_CONSTRAINT,
Child.TABLE_NAME Child_TABLE,
Child.CONSTRAINT_NAME Child_CONSTRAINT,
CHILD.Delete_Rule,
CHILD.Status
from All_CONSTRAINTS Child, All_CONSTRAINTS Parent
where Child.R_CONSTRAINT_NAME = Parent.CONSTRAINT_NAME
and Child.TABLE_NAME != Parent.TABLE_NAME
and Child.TABLE_NAME = upper('&Child_Table_Name')
ORDER BY Parent.TABLE_NAME
[/sql]

[sql]
select Parent.TABLE_NAME Parent_TABLE,
Parent.CONSTRAINT_NAME Parent_CONSTRAINT,
RTRIM(Child.TABLE_NAME) Child_TABLE, atc.comments AS Table_Desc,
Child.CONSTRAINT_NAME Child_CONSTRAINT,
RTRIM(col.COLUMN_NAME) AS Child_Col, col.POSITION AS Child_Col_Position
,CHILD.Delete_Rule, CHILD.Status
from All_CONSTRAINTS Child, All_CONSTRAINTS Parent, sys.all_tab_comments atc, ALL_CONS_COLUMNS col
where Child.R_CONSTRAINT_NAME = Parent.CONSTRAINT_NAME
and Child.TABLE_NAME != Parent.TABLE_NAME
and Parent.TABLE_NAME = upper('&Parent_Table_Name')
AND Child.owner = atc.Owner(+)
AND Child.TABLE_NAME = atc.table_name(+)
AND col.owner = child.owner
AND Child.constraint_name = col.constraint_name
ORDER BY Child.TABLE_NAME[/sql]
Reply With Quote