外键及其在Oracle DB中引用的表的列表
外键及其在Oracle DB中引用的表的列表
提问于 2009-11-13 23:37:45
我正在尝试查找一个查询，该查询将返回表的外键及其引用的表和列的列表。我已经走到一半了
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'但是我仍然需要知道这个键引用了哪个表和主键。我怎么才能得到它呢？
回答 15
Stack Overflow用户
回答已采纳
发布于 2009-11-13 23:46:39
引用的主键在表ALL_CONSTRAINTS的列r_owner和r_constraint_name中进行了描述。这将为您提供所需的信息：
SELECT a.table_name, a.column_name, a.constraint_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 = :TableNameStack Overflow用户
发布于 2014-12-03 21:48:40
试试这个：
select * from all_constraints where r_constraint_name in (select constraint_name
from all_constraints where table_name='YOUR_TABLE_NAME');Stack Overflow用户
发布于 2009-11-14 02:31:44
这是我们使用的一个通用脚本，它非常方便。
将它保存下来，这样您就可以直接执行它(@fkeys.sql)。它将允许您按所有者和父表或子表进行搜索，并显示外键关系。当前脚本确实显式地假脱机到C:\SQLRPTS，因此您将需要创建将该行更改为您想要使用的内容的文件夹。
REM ########################################################################
REM ##
REM ## fkeys.sql
REM ##
REM ## Displays the foreign key relationships
REM ##
REM #######################################################################
CLEAR BREAK
CLEAR COL
SET LINES 200
SET PAGES 54
SET NEWPAGE 0
SET WRAP OFF
SET VERIFY OFF
SET FEEDBACK OFF
break on table_name skip 2 on constraint_name on r_table_name skip 1
column CHILDCOL format a60 head 'CHILD COLUMN'
column PARENTCOL format a60 head 'PARENT COLUMN'
column constraint_name format a30 head 'FK CONSTRAINT NAME'
column delete_rule format a15
column bt noprint
column bo noprint
TTITLE LEFT _DATE CENTER 'FOREIGN KEY RELATIONSHIPS ON &new_prompt' RIGHT 'PAGE:'FORMAT 999 SQL.PNO SKIP 2
SPOOL C:\SQLRPTS\FKeys_&new_prompt
ACCEPT OWNER_NAME PROMPT 'Enter Table Owner (or blank for all): '
ACCEPT PARENT_TABLE_NAME PROMPT 'Enter Parent Table or leave blank for all: '
ACCEPT CHILD_TABLE_NAME PROMPT 'Enter Child Table or leave blank for all: '
select b.owner || '.' || b.table_name || '.' || b.column_name CHILDCOL,
b.position,
c.owner || '.' || c.table_name || '.' || c.column_name PARENTCOL,
a.constraint_name,
a.delete_rule,
b.table_name bt,
b.owner bo
from all_cons_columns b,
all_cons_columns c,
all_constraints a
where b.constraint_name = a.constraint_name
and a.owner = b.owner
and b.position = c.position
and c.constraint_name = a.r_constraint_name
and c.owner = a.r_owner
and a.constraint_type = 'R'
and c.owner like case when upper('&OWNER_NAME') is null then '%'
else upper('&OWNER_NAME') end
and c.table_name like case when upper('&PARENT_TABLE_NAME') is null then '%'
else upper('&PARENT_TABLE_NAME') end
and b.table_name like case when upper('&CHILD_TABLE_NAME') is null then '%'
else upper('&CHILD_TABLE_NAME') end
order by 7,6,4,2
/
SPOOL OFF
TTITLE OFF
SET FEEDBACK ON
SET VERIFY ON
CLEAR BREAK
CLEAR COL
SET PAGES 24
SET LINES 100
SET NEWPAGE 1
UNDEF OWNER页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1729996
复制相关文章
相似问题