我试图找到一个查询，它将返回一个表的外键列表和他们引用的表和列的列表。我在那里一半
SELECT a.table_name,a.column_name,a.constraint_name,c.owner FROM ALL_CONS_COLUMNS A,ALL_CONSTRAINTS C where A.CONSTRAINT_NAME = C.CONSTRAINT_NAME and a.table_name=:TableName and C.CONSTRAINT_TYPE = 'R'
但是我仍然需要知道这个键引用了哪个表和主键。我怎么会这样？
引用的主键在表ALL_CONSTRAINTS的列r_owner和r_constraint_name中描述。这将给你所需的信息：
SELECT a.table_name,c.owner,-- referenced pk
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 c.constraint_type = 'R'
AND a.table_name = :TableName
