blocks|key|1192442|text|引用的主键在表ALL_CONSTRAINTS的列r_owner和r_constraint_name中进行了描述。这将为您提供所需的信息：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1192443|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+=+:TableName|code-block|syntax|javascript|1192444|entityMap^0|7|F|O|7|W|H|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

The referenced primary key is described in the columns <code>r_owner</code> and <code>r_constraint_name</code> of the table <code>ALL_CONSTRAINTS</code>. This will give you the info you want:

<pre><code>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 = :TableName
</code></pre>

blocks|key|1192475|text|这是我们使用的一个通用脚本，它非常方便。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1192476|将它保存下来，这样您就可以直接执行它(@fkeys.sql)。它将允许您按所有者和父表或子表进行搜索，并显示外键关系。当前脚本确实显式地假脱机到C:\SQLRPTS，因此您将需要创建将该行更改为您想要使用的内容的文件夹。|1192477|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+%7C%7C+'.'+%7C%7C+b.table_name+%7C%7C+'.'+%7C%7C+b.column_name+CHILDCOL,
+++++++++b.position,
+++++++++c.owner+%7C%7C+'.'+%7C%7C+c.table_name+%7C%7C+'.'+%7C%7C+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+'%25'
++++++++++++++++++++++++++++++++else+upper('&OWNER_NAME')+end
+++++and+c.table_name+like+case+when+upper('&PARENT_TABLE_NAME')+is+null+then+'%25'
++++++++++++++++++++++++++++++++else+upper('&PARENT_TABLE_NAME')+end
+++++and+b.table_name+like+case+when+upper('&CHILD_TABLE_NAME')+is+null+then+'%25'
++++++++++++++++++++++++++++++++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|code-block|syntax|javascript|1192478|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here is an all-purpose script we use that has been incredibly handy. 

Save it off so you can execute it directly (@fkeys.sql). It will let you search by Owner and either the Parent or Child table and show foreign key relationships. The current script does explicitly spool to C:\SQLRPTS so you will need to create that folder of change that line to something you want to use.

<pre><code>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 &amp;new_prompt' RIGHT 'PAGE:'FORMAT 999 SQL.PNO SKIP 2

SPOOL C:\SQLRPTS\FKeys_&amp;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('&amp;OWNER_NAME') is null then '%'
 else upper('&amp;OWNER_NAME') end
 and c.table_name like case when upper('&amp;PARENT_TABLE_NAME') is null then '%'
 else upper('&amp;PARENT_TABLE_NAME') end
 and b.table_name like case when upper('&amp;CHILD_TABLE_NAME') is null then '%'
 else upper('&amp;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
</code></pre>

blocks|key|1407897|text|这是另一种解决方案。使用sys的默认视图非常慢(在我的情况下大约是10秒)。这比那要快得多(约0.5s)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1407898|SELECT
++++CONST.NAME+AS+CONSTRAINT_NAME,
++++RCONST.NAME+AS+REF_CONSTRAINT_NAME,

++++OBJ.NAME+AS+TABLE_NAME,
++++COALESCE(ACOL.NAME,+COL.NAME)+AS+COLUMN_NAME,
++++CCOL.POS#+AS+POSITION,

++++ROBJ.NAME+AS+REF_TABLE_NAME,
++++COALESCE(RACOL.NAME,+RCOL.NAME)+AS+REF_COLUMN_NAME,
++++RCCOL.POS#+AS+REF_POSITION
FROM+SYS.CON$+CONST
INNER+JOIN+SYS.CDEF$+CDEF+ON+CDEF.CON#+=+CONST.CON#
INNER+JOIN+SYS.CCOL$+CCOL+ON+CCOL.CON#+=+CONST.CON#
INNER+JOIN+SYS.COL$+COL++ON+(CCOL.OBJ#+=+COL.OBJ#)+AND+(CCOL.INTCOL#+=+COL.INTCOL#)
INNER+JOIN+SYS.OBJ$+OBJ+ON+CCOL.OBJ#+=+OBJ.OBJ#
LEFT+JOIN+SYS.ATTRCOL$+ACOL+ON+(CCOL.OBJ#+=+ACOL.OBJ#)+AND+(CCOL.INTCOL#+=+ACOL.INTCOL#)

INNER+JOIN+SYS.CON$+RCONST+ON+RCONST.CON#+=+CDEF.RCON#
INNER+JOIN+SYS.CCOL$+RCCOL+ON+RCCOL.CON#+=+RCONST.CON#
INNER+JOIN+SYS.COL$+RCOL++ON+(RCCOL.OBJ#+=+RCOL.OBJ#)+AND+(RCCOL.INTCOL#+=+RCOL.INTCOL#)
INNER+JOIN+SYS.OBJ$+ROBJ+ON+RCCOL.OBJ#+=+ROBJ.OBJ#
LEFT+JOIN+SYS.ATTRCOL$+RACOL++ON+(RCCOL.OBJ#+=+RACOL.OBJ#)+AND+(RCCOL.INTCOL#+=+RACOL.INTCOL#)

WHERE+CONST.OWNER#+=+userenv('SCHEMAID')
++AND+RCONST.OWNER#+=+userenv('SCHEMAID')
++AND+CDEF.TYPE#+=+4++/*+'R'+Referential/Foreign+Key+*/;|code-block|syntax|javascript|1407899|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is an another solution. Using sys's default views are so slow (approx 10s in my situation). This is much faster than that (approx. 0.5s).

<pre><code>SELECT
 CONST.NAME AS CONSTRAINT_NAME,
 RCONST.NAME AS REF_CONSTRAINT_NAME,

 OBJ.NAME AS TABLE_NAME,
 COALESCE(ACOL.NAME, COL.NAME) AS COLUMN_NAME,
 CCOL.POS# AS POSITION,

 ROBJ.NAME AS REF_TABLE_NAME,
 COALESCE(RACOL.NAME, RCOL.NAME) AS REF_COLUMN_NAME,
 RCCOL.POS# AS REF_POSITION
FROM SYS.CON$ CONST
INNER JOIN SYS.CDEF$ CDEF ON CDEF.CON# = CONST.CON#
INNER JOIN SYS.CCOL$ CCOL ON CCOL.CON# = CONST.CON#
INNER JOIN SYS.COL$ COL ON (CCOL.OBJ# = COL.OBJ#) AND (CCOL.INTCOL# = COL.INTCOL#)
INNER JOIN SYS.OBJ$ OBJ ON CCOL.OBJ# = OBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ ACOL ON (CCOL.OBJ# = ACOL.OBJ#) AND (CCOL.INTCOL# = ACOL.INTCOL#)

INNER JOIN SYS.CON$ RCONST ON RCONST.CON# = CDEF.RCON#
INNER JOIN SYS.CCOL$ RCCOL ON RCCOL.CON# = RCONST.CON#
INNER JOIN SYS.COL$ RCOL ON (RCCOL.OBJ# = RCOL.OBJ#) AND (RCCOL.INTCOL# = RCOL.INTCOL#)
INNER JOIN SYS.OBJ$ ROBJ ON RCCOL.OBJ# = ROBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ RACOL ON (RCCOL.OBJ# = RACOL.OBJ#) AND (RCCOL.INTCOL# = RACOL.INTCOL#)

WHERE CONST.OWNER# = userenv('SCHEMAID')
 AND RCONST.OWNER# = userenv('SCHEMAID')
 AND CDEF.TYPE# = 4 /* 'R' Referential/Foreign Key */;
</code></pre>

blocks|key|1407923|text|如果需要用户的所有外键，请使用以下脚本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1407924|SELECT+a.constraint_name,+a.table_name,+a.column_name,++c.owner,+
+++++++c_pk.table_name+r_table_name,++b.column_name+r_column_name
++FROM+user_cons_columns+a
++JOIN+user_constraints+c+ON+a.owner+=+c.owner
+++++++AND+a.constraint_name+=+c.constraint_name
++JOIN+user_constraints+c_pk+ON+c.r_owner+=+c_pk.owner
+++++++AND+c.r_constraint_name+=+c_pk.constraint_name
++JOIN+user_cons_columns+b+ON+C_PK.owner+=+b.owner
+++++++AND++C_PK.CONSTRAINT_NAME+=+b.constraint_name+AND+b.POSITION+=+a.POSITION+++++
+WHERE+c.constraint_type+=+'R'|code-block|syntax|javascript|1407925|基于Vincent+Malgrat代码|1407926|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you need all the foreign keys of the user then use the following script

<pre><code>SELECT a.constraint_name, a.table_name, a.column_name, c.owner, 
 c_pk.table_name r_table_name, b.column_name r_column_name
 FROM user_cons_columns a
 JOIN user_constraints c ON a.owner = c.owner
 AND a.constraint_name = c.constraint_name
 JOIN user_constraints c_pk ON c.r_owner = c_pk.owner
 AND c.r_constraint_name = c_pk.constraint_name
 JOIN user_cons_columns b ON C_PK.owner = b.owner
 AND C_PK.CONSTRAINT_NAME = b.constraint_name AND b.POSITION = a.POSITION 
 WHERE c.constraint_type = 'R'
</code></pre>

based on Vincent Malgrat code

blocks|key|1407950|text|这将遍历给定表和列的外键层次结构，并返回子表和孙表以及所有子表中的列。它使用子查询将r_table_name和r_column_name添加到user_constraints，然后使用它们连接行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1407951|select+distinct+table_name,+constraint_name,+column_name,+r_table_name,+position,+constraint_type+
from+(
++++SELECT+uc.table_name,+
++++uc.constraint_name,+
++++cols.column_name,+
++++(select+table_name+from+user_constraints+where+constraint_name+=+uc.r_constraint_name)+
++++++++r_table_name,
++++(select+column_name+from+user_cons_columns+where+constraint_name+=+uc.r_constraint_name+and+position+=+cols.position)+
++++++++r_column_name,
++++cols.position,
++++uc.constraint_type
++++FROM+user_constraints+uc
++++inner+join+user_cons_columns+cols+on+uc.constraint_name+=+cols.constraint_name+
++++where+constraint_type+!=+'C'
)+
start+with+table_name+=+'MY_TABLE_NAME'+and+column_name+=+'MY_COLUMN_NAME'++
connect+by+nocycle+
prior+table_name+=+r_table_name+
and+prior+column_name+=+r_column_name;|code-block|syntax|javascript|1407952|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will travel the hierarchy of foreign keys for a given table and column and return columns from child and grandchild, and all descendant tables. It uses sub-queries to add r_table_name and r_column_name to user_constraints, and then uses them to connect rows. 

<pre><code>select distinct table_name, constraint_name, column_name, r_table_name, position, constraint_type 
from (
 SELECT uc.table_name, 
 uc.constraint_name, 
 cols.column_name, 
 (select table_name from user_constraints where constraint_name = uc.r_constraint_name) 
 r_table_name,
 (select column_name from user_cons_columns where constraint_name = uc.r_constraint_name and position = cols.position) 
 r_column_name,
 cols.position,
 uc.constraint_type
 FROM user_constraints uc
 inner join user_cons_columns cols on uc.constraint_name = cols.constraint_name 
 where constraint_type != 'C'
) 
start with table_name = 'MY_TABLE_NAME' and column_name = 'MY_COLUMN_NAME' 
connect by nocycle 
prior table_name = r_table_name 
and prior column_name = r_column_name;
</code></pre>

blocks|key|1192578|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1192579|select+*+from+all_constraints+where+r_constraint_name+in+(select+constraint_name+
from+all_constraints+where+table_name='YOUR_TABLE_NAME');|code-block|syntax|javascript|1192580|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>select * from all_constraints where r_constraint_name in (select constraint_name 
from all_constraints where table_name='YOUR_TABLE_NAME');
</code></pre>

blocks|key|1407983|text|如果要从UAT环境表创建FK约束为Live，请在dynamic+query下触发.....|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1407984|++++SELECT+'ALTER+TABLE+'%7C%7COBJ.NAME%7C%7C'+ADD+CONSTRAINT+'%7C%7CCONST.NAME%7C%7C'+++++FOREIGN+KEY+('%7C%7CCOALESCE(ACOL.NAME,+COL.NAME)%7C%7C')+REFERENCES+'
%7C%7CROBJ.NAME+%7C%7C'+('%7C%7CCOALESCE(RACOL.NAME,+RCOL.NAME)+%7C%7C');'
FROM+SYS.CON$+CONST
INNER+JOIN+SYS.CDEF$+CDEF+ON+CDEF.CON#+=+CONST.CON#
INNER+JOIN+SYS.CCOL$+CCOL+ON+CCOL.CON#+=+CONST.CON#
INNER+JOIN+SYS.COL$+COL++ON+(CCOL.OBJ#+=+COL.OBJ#)+AND+(CCOL.INTCOL#+=+++++COL.INTCOL#)
INNER+JOIN+SYS.OBJ$+OBJ+ON+CCOL.OBJ#+=+OBJ.OBJ#
LEFT+JOIN+SYS.ATTRCOL$+ACOL+ON+(CCOL.OBJ#+=+ACOL.OBJ#)+AND+(CCOL.INTCOL#+=+++++ACOL.INTCOL#)

INNER+JOIN+SYS.CON$+RCONST+ON+RCONST.CON#+=+CDEF.RCON#
INNER+JOIN+SYS.CCOL$+RCCOL+ON+RCCOL.CON#+=+RCONST.CON#
INNER+JOIN+SYS.COL$+RCOL++ON+(RCCOL.OBJ#+=+RCOL.OBJ#)+AND+(RCCOL.INTCOL#+=+++++RCOL.INTCOL#)
INNER+JOIN+SYS.OBJ$+ROBJ+ON+RCCOL.OBJ#+=+ROBJ.OBJ#
LEFT+JOIN+SYS.ATTRCOL$+RACOL++ON+(RCCOL.OBJ#+=+RACOL.OBJ#)+AND+++++(RCCOL.INTCOL#+=+RACOL.INTCOL#)

WHERE+CONST.OWNER#+=+userenv('SCHEMAID')
AND+RCONST.OWNER#+=+userenv('SCHEMAID')
AND+CDEF.TYPE#+=+4+
AND+OBJ.NAME+=+<table_name>;|code-block|syntax|javascript|1407985|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In case one wants to create FK constraints from UAT environment table to Live, fire below dynamic query.....

<pre><code> SELECT 'ALTER TABLE '||OBJ.NAME||' ADD CONSTRAINT '||CONST.NAME||' FOREIGN KEY ('||COALESCE(ACOL.NAME, COL.NAME)||') REFERENCES '
||ROBJ.NAME ||' ('||COALESCE(RACOL.NAME, RCOL.NAME) ||');'
FROM SYS.CON$ CONST
INNER JOIN SYS.CDEF$ CDEF ON CDEF.CON# = CONST.CON#
INNER JOIN SYS.CCOL$ CCOL ON CCOL.CON# = CONST.CON#
INNER JOIN SYS.COL$ COL ON (CCOL.OBJ# = COL.OBJ#) AND (CCOL.INTCOL# = COL.INTCOL#)
INNER JOIN SYS.OBJ$ OBJ ON CCOL.OBJ# = OBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ ACOL ON (CCOL.OBJ# = ACOL.OBJ#) AND (CCOL.INTCOL# = ACOL.INTCOL#)

INNER JOIN SYS.CON$ RCONST ON RCONST.CON# = CDEF.RCON#
INNER JOIN SYS.CCOL$ RCCOL ON RCCOL.CON# = RCONST.CON#
INNER JOIN SYS.COL$ RCOL ON (RCCOL.OBJ# = RCOL.OBJ#) AND (RCCOL.INTCOL# = RCOL.INTCOL#)
INNER JOIN SYS.OBJ$ ROBJ ON RCCOL.OBJ# = ROBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ RACOL ON (RCCOL.OBJ# = RACOL.OBJ#) AND (RCCOL.INTCOL# = RACOL.INTCOL#)

WHERE CONST.OWNER# = userenv('SCHEMAID')
AND RCONST.OWNER# = userenv('SCHEMAID')
AND CDEF.TYPE# = 4 
AND OBJ.NAME = &lt;table_name&gt;;
</code></pre>

blocks|key|1192638|text|select+d.table_name,

+++++++d.constraint_name+"Primary+Constraint+Name",

+++++++b.constraint_name+"Referenced+Constraint+Name"

from+user_constraints+d,

+++++(select+c.constraint_name,

+++++++++++++c.r_constraint_name,

+++++++++++++c.table_name

++++++from+user_constraints+c+

++++++where+table_name='EMPLOYEES'+--your+table+name+instead+of+EMPLOYEES

++++++and+constraint_type='R')+b

where+d.constraint_name=b.r_constraint_name|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1192639|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select d.table_name,

 d.constraint_name "Primary Constraint Name",

 b.constraint_name "Referenced Constraint Name"

from user_constraints d,

 (select c.constraint_name,

 c.r_constraint_name,

 c.table_name

 from user_constraints c 

 where table_name='EMPLOYEES' --your table name instead of EMPLOYEES

 and constraint_type='R') b

where d.constraint_name=b.r_constraint_name
</code></pre>

blocks|key|1408029|text|我知道现在回答有点晚，但不管怎样，让我来回答，上面的一些答案相当复杂，因此这里是一个简单得多的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1408030|SELECT+a.table_name+child_table,+a.column_name+child_column,+a.constraint_name,+
++++++b.table_name+parent_table,+b.column_name+parent_column
++FROM+all_cons_columns+a
++JOIN+all_constraints+c+ON+a.owner+=+c.owner+AND+a.constraint_name+=+c.constraint_name
+join+all_cons_columns+b+on+c.owner+=+b.owner+and+c.r_constraint_name+=+b.constraint_name
+WHERE+c.constraint_type+=+'R'
+++AND+a.table_name+=+'your+table+name'|code-block|syntax|javascript|1408031|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I know it's kinda late to answer but let me answer anyway, some of the answers
above are quite complicated hence here is a much simpler take. 



<pre><code>SELECT a.table_name child_table, a.column_name child_column, a.constraint_name, 
 b.table_name parent_table, b.column_name parent_column
 FROM all_cons_columns a
 JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name
 join all_cons_columns b on c.owner = b.owner and c.r_constraint_name = b.constraint_name
 WHERE c.constraint_type = 'R'
 AND a.table_name = 'your table name'
</code></pre>

blocks|key|1408049|text|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+:=TABLE_NAME
+++AND+c.owner+:=OWNER_NAME;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1408050|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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 :=TABLE_NAME
 AND c.owner :=OWNER_NAME;
</code></pre>

blocks|key|1192704|text|WITH+reference_view+AS
+++++(SELECT+a.owner,+a.table_name,+a.constraint_name,+a.constraint_type,
+++++++++++++a.r_owner,+a.r_constraint_name,+b.column_name
++++++++FROM+dba_constraints+a,+dba_cons_columns+b
+++++++WHERE++a.owner+LIKE+UPPER+('SYS')+AND
++++++++++a.owner+=+b.owner
+++++++++AND+a.constraint_name+=+b.constraint_name
+++++++++AND+constraint_type+=+'R'),
+++++constraint_view+AS
+++++(SELECT+a.owner+a_owner,+a.table_name,+a.column_name,+b.owner+b_owner,
+++++++++++++b.constraint_name
++++++++FROM+dba_cons_columns+a,+dba_constraints+b
+++++++WHERE+a.owner+=+b.owner
+++++++++AND+a.constraint_name+=+b.constraint_name
+++++++++AND+b.constraint_type+=+'P'
+++++++++AND+a.owner+LIKE+UPPER+('SYS')
+++++++++)
SELECT++
+++++++rv.table_name+FK_Table+,+rv.column_name+FK_Column+,
+++++++CV.table_name+PK_Table+,+rv.column_name+PK_Column+,+rv.r_constraint_name+Constraint_Name+
++FROM+reference_view+rv,+constraint_view+CV
+WHERE+rv.r_constraint_name+=+CV.constraint_name+AND+rv.r_owner+=+CV.b_owner;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1192705|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>WITH reference_view AS
 (SELECT a.owner, a.table_name, a.constraint_name, a.constraint_type,
 a.r_owner, a.r_constraint_name, b.column_name
 FROM dba_constraints a, dba_cons_columns b
 WHERE a.owner LIKE UPPER ('SYS') AND
 a.owner = b.owner
 AND a.constraint_name = b.constraint_name
 AND constraint_type = 'R'),
 constraint_view AS
 (SELECT a.owner a_owner, a.table_name, a.column_name, b.owner b_owner,
 b.constraint_name
 FROM dba_cons_columns a, dba_constraints b
 WHERE a.owner = b.owner
 AND a.constraint_name = b.constraint_name
 AND b.constraint_type = 'P'
 AND a.owner LIKE UPPER ('SYS')
 )
SELECT 
 rv.table_name FK_Table , rv.column_name FK_Column ,
 CV.table_name PK_Table , rv.column_name PK_Column , rv.r_constraint_name Constraint_Name 
 FROM reference_view rv, constraint_view CV
 WHERE rv.r_constraint_name = CV.constraint_name AND rv.r_owner = CV.b_owner;
</code></pre>

blocks|key|1192747|text|For+Load+UserTable+(外键及其引用的表的列表)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1192748|WITH

reference_view+AS
+++++(SELECT+a.owner,+a.table_name,+a.constraint_name,+a.constraint_type,
+++++++++++++a.r_owner,+a.r_constraint_name,+b.column_name
++++++++FROM+dba_constraints+a,+dba_cons_columns+b
+++++++WHERE+
++++++++++a.owner+=+b.owner
+++++++++AND+a.constraint_name+=+b.constraint_name
+++++++++AND+constraint_type+=+'R'),
constraint_view+AS
+++++(SELECT+a.owner+a_owner,+a.table_name,+a.column_name,+b.owner+b_owner,
+++++++++++++b.constraint_name
++++++++FROM+dba_cons_columns+a,+dba_constraints+b
+++++++WHERE+a.owner+=+b.owner
+++++++++AND+a.constraint_name+=+b.constraint_name
+++++++++AND+b.constraint_type+=+'P'

+++++++++)+,
usertableviewlist+AS+
(
++++++select++TABLE_NAME++from+user_tables++
)+
SELECT++
+++++++rv.table_name+FK_Table+,+rv.column_name+FK_Column+,
+++++++CV.table_name+PK_Table+,+rv.column_name+PK_Column+,+rv.r_constraint_name+Constraint_Name+
++FROM+reference_view+rv,+constraint_view+CV+,+usertableviewlist+UTable
+WHERE+rv.r_constraint_name+=+CV.constraint_name+AND+rv.r_owner+=+CV.b_owner+And+UTable.TABLE_NAME+=+rv.table_name;+|code-block|syntax|javascript|1192749|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For Load UserTable (List of foreign keys and the tables they reference)

<pre><code>WITH

reference_view AS
 (SELECT a.owner, a.table_name, a.constraint_name, a.constraint_type,
 a.r_owner, a.r_constraint_name, b.column_name
 FROM dba_constraints a, dba_cons_columns b
 WHERE 
 a.owner = b.owner
 AND a.constraint_name = b.constraint_name
 AND constraint_type = 'R'),
constraint_view AS
 (SELECT a.owner a_owner, a.table_name, a.column_name, b.owner b_owner,
 b.constraint_name
 FROM dba_cons_columns a, dba_constraints b
 WHERE a.owner = b.owner
 AND a.constraint_name = b.constraint_name
 AND b.constraint_type = 'P'

 ) ,
usertableviewlist AS 
(
 select TABLE_NAME from user_tables 
) 
SELECT 
 rv.table_name FK_Table , rv.column_name FK_Column ,
 CV.table_name PK_Table , rv.column_name PK_Column , rv.r_constraint_name Constraint_Name 
 FROM reference_view rv, constraint_view CV , usertableviewlist UTable
 WHERE rv.r_constraint_name = CV.constraint_name AND rv.r_owner = CV.b_owner And UTable.TABLE_NAME = rv.table_name; 
</code></pre>

blocks|key|1192805|text|我的版本，在我的拙见中，更具可读性：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1192806|SELECT+++PARENT.TABLE_NAME++"PARENT+TABLE_NAME"
,++++++++PARENT.CONSTRAINT_NAME++"PARENT+PK+CONSTRAINT"
,+++++++'->'+"+"
,++++++++CHILD.TABLE_NAME++"CHILD+TABLE_NAME"
,++++++++CHILD.COLUMN_NAME++"CHILD+COLUMN_NAME"
,++++++++CHILD.CONSTRAINT_NAME++"CHILD+CONSTRAINT_NAME"
FROM+++++ALL_CONS_COLUMNS+++CHILD
,++++++++ALL_CONSTRAINTS+++CT
,++++++++ALL_CONSTRAINTS+++PARENT
WHERE++++CHILD.OWNER++=++CT.OWNER
AND++++++CT.CONSTRAINT_TYPE++=+'R'
AND++++++CHILD.CONSTRAINT_NAME++=++CT.CONSTRAINT_NAME+
AND++++++CT.R_OWNER++=++PARENT.OWNER
AND++++++CT.R_CONSTRAINT_NAME++=++PARENT.CONSTRAINT_NAME+
AND++++++CHILD.TABLE_NAME++=+::table+--+table+name+variable
AND++++++CT.OWNER++=+::owner;+--+schema+variable,+could+not+be+needed|code-block|syntax|javascript|1192807|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

My version, in my humble opinion, more readable:

<pre><code>SELECT PARENT.TABLE_NAME "PARENT TABLE_NAME"
, PARENT.CONSTRAINT_NAME "PARENT PK CONSTRAINT"
, '-&gt;' " "
, CHILD.TABLE_NAME "CHILD TABLE_NAME"
, CHILD.COLUMN_NAME "CHILD COLUMN_NAME"
, CHILD.CONSTRAINT_NAME "CHILD CONSTRAINT_NAME"
FROM ALL_CONS_COLUMNS CHILD
, ALL_CONSTRAINTS CT
, ALL_CONSTRAINTS PARENT
WHERE CHILD.OWNER = CT.OWNER
AND CT.CONSTRAINT_TYPE = 'R'
AND CHILD.CONSTRAINT_NAME = CT.CONSTRAINT_NAME 
AND CT.R_OWNER = PARENT.OWNER
AND CT.R_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME 
AND CHILD.TABLE_NAME = ::table -- table name variable
AND CT.OWNER = ::owner; -- schema variable, could not be needed
</code></pre>

blocks|key|1192832|text|现在回答有点晚了，但我希望我的答案对需要选择组合外键的人有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1192833|SELECT
++++"C"."CONSTRAINT_NAME",
++++"C"."OWNER"+AS+"SCHEMA_NAME",
++++"C"."TABLE_NAME",
++++"COL"."COLUMN_NAME",
++++"REF_COL"."OWNER"+AS+"REF_SCHEMA_NAME",
++++"REF_COL"."TABLE_NAME"+AS+"REF_TABLE_NAME",
++++"REF_COL"."COLUMN_NAME"+AS+"REF_COLUMN_NAME"
FROM
++++"USER_CONSTRAINTS"+"C"
INNER+JOIN+"USER_CONS_COLUMNS"+"COL"+ON+"COL"."OWNER"+=+"C"."OWNER"
+AND+"COL"."CONSTRAINT_NAME"+=+"C"."CONSTRAINT_NAME"
INNER+JOIN+"USER_CONS_COLUMNS"+"REF_COL"+ON+"REF_COL"."OWNER"+=+"C"."R_OWNER"
+AND+"REF_COL"."CONSTRAINT_NAME"+=+"C"."R_CONSTRAINT_NAME"
+AND+"REF_COL"."POSITION"+=+"COL"."POSITION"
WHERE+"C"."TABLE_NAME"+=+'TableName'+AND+"C"."CONSTRAINT_TYPE"+=+'R'|code-block|syntax|javascript|1192834|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Its a bit late to anwser, but I hope my answer been useful for someone, who needs to select Composite foreign keys. 

<pre><code>SELECT
 "C"."CONSTRAINT_NAME",
 "C"."OWNER" AS "SCHEMA_NAME",
 "C"."TABLE_NAME",
 "COL"."COLUMN_NAME",
 "REF_COL"."OWNER" AS "REF_SCHEMA_NAME",
 "REF_COL"."TABLE_NAME" AS "REF_TABLE_NAME",
 "REF_COL"."COLUMN_NAME" AS "REF_COLUMN_NAME"
FROM
 "USER_CONSTRAINTS" "C"
INNER JOIN "USER_CONS_COLUMNS" "COL" ON "COL"."OWNER" = "C"."OWNER"
 AND "COL"."CONSTRAINT_NAME" = "C"."CONSTRAINT_NAME"
INNER JOIN "USER_CONS_COLUMNS" "REF_COL" ON "REF_COL"."OWNER" = "C"."R_OWNER"
 AND "REF_COL"."CONSTRAINT_NAME" = "C"."R_CONSTRAINT_NAME"
 AND "REF_COL"."POSITION" = "COL"."POSITION"
WHERE "C"."TABLE_NAME" = 'TableName' AND "C"."CONSTRAINT_TYPE" = 'R'
</code></pre>

blocks|key|1192936|text|我使用了下面的代码，它达到了我的目的-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1192937|SELECT+fk.owner,+fk.table_name,+col.column_name
FROM+dba_constraints+pk,+dba_constraints+fk,+dba_cons_columns+col
WHERE+pk.constraint_name+=+fk.r_constraint_name
AND+fk.constraint_name+=+col.constraint_name
AND+pk.owner+=+col.owner
AND+pk.owner+=+fk.owner
AND+fk.constraint_type+=+'R'+++
AND+pk.owner+=+sys_context('USERENV',+'CURRENT_SCHEMA')+
AND+pk.table_name+=+:my_table
AND+pk.constraint_type+=+'P';|code-block|syntax|javascript|1192938|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I used below code and it served my purpose-

<pre><code>SELECT fk.owner, fk.table_name, col.column_name
FROM dba_constraints pk, dba_constraints fk, dba_cons_columns col
WHERE pk.constraint_name = fk.r_constraint_name
AND fk.constraint_name = col.constraint_name
AND pk.owner = col.owner
AND pk.owner = fk.owner
AND fk.constraint_type = 'R' 
AND pk.owner = sys_context('USERENV', 'CURRENT_SCHEMA') 
AND pk.table_name = :my_table
AND pk.constraint_type = 'P';
</code></pre>

I'm trying to find a query which will return me a list of the foreign keys for a table and the tables and columns they reference. I am half way there with 

<pre><code>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'
</code></pre>

But I still need to know which table and primary key are referenced by this key. How would I get that?

List of foreign keys and the tables they reference in Oracle DB

我正在尝试查找一个查询，该查询将返回表的外键及其引用的表和列的列表。我已经走到一半了SELECT a.table_name,        a.column_name,        a.constraint_name,        c.ownerFROM ALL_CONS_COLUMNS A, ALL_CONSTR...

问外键及其在Oracle DB中引用的表的列表
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问外键及其在Oracle DB中引用的表的列表EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问外键及其在Oracle DB中引用的表的列表
EN