如何在 Oracle SQL Developer 中找到哪些表引用了给定的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1143728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I find which tables reference a given table in Oracle SQL Developer?
提问by Rudd Zwolinski
In Oracle SQL Developer, if I'm viewing the information on a table, I can view the constraints, which let me see the foreign keys (and thus which tables are referenced by this table), and I can view the dependencies to see what packages and such reference the table. But I'm not sure how to find which tables reference the table.
在Oracle SQL Developer 中，如果我正在查看表上的信息，我可以查看约束，这让我可以看到外键（以及该表引用了哪些表），并且我可以查看依赖关系以查看什么包等参考表。但我不确定如何找到哪些表引用了该表。
For example, say I'm looking at the emptable. There is another table emp_deptwhich captures which employees work in which departments, which references the emptable through emp_id, the primary key of the emptable. Is there a way (through some UI element in the program, not through SQL) to find that the emp_depttable references the emptable, without me having to know that the emp_depttable exists?
例如，假设我正在看emp桌子。还有另一个表emp_dept，它记录了哪些员工在哪些部门工作，它emp通过表emp_id的主键引用该emp表。有没有办法（通过程序中的某些 UI 元素，而不是通过 SQL）找到该emp_dept表引用该emp表，而我不必知道该emp_dept表是否存在？
回答by FerranB
No. There is no such option available from Oracle SQL Developer.
不可以。Oracle SQL Developer 没有提供这样的选项。
You have to execute a query by hand or use other tool (For instance PLSQL Developerhas such option). The following SQL is that one used by PLSQL Developer:
您必须手动执行查询或使用其他工具（例如PLSQL Developer有这样的选项）。以下 SQL 是 PLSQL Developer 使用的 SQL：
select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in ('P', 'U')
and table_name = :r_table_name
and owner = :r_owner
)
order by table_name, constraint_name
Where r_owneris the schema, and r_table_nameis the table for which you are looking for references. The names are case sensitive
r_owner架构在哪里，r_table_name是您要查找参考的表。名称区分大小写
Be careful because on the reports tab of Oracle SQL Developer there is the option "All tables / Dependencies" this is from ALL_DEPENDENCIESwhich refers to "dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links.". Then, this report have no value for your question.
请注意，因为在 Oracle SQL Developer 的报告选项卡上有一个选项“所有表/依赖项”，它来自ALL_DEPENDENCIES，它指的是“当前用户可访问的过程、包、函数、包体和触发器之间的依赖关系，包括依赖关系在没有任何数据库链接的情况下创建的视图。” . 那么，这份报告对你的问题没有价值。
回答by junaling
To add this to SQL Developer as an extension do the following:
要将其作为扩展添加到 SQL Developer，请执行以下操作：
- Save the below code into an xml file (e.g. fk_ref.xml):
- 将以下代码保存到一个 xml 文件中（例如 fk_ref.xml）：
<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[FK References]]></title>
<query>
<sql>
<![CDATA[select a.owner,
a.table_name,
a.constraint_name,
a.status
from all_constraints a
where a.constraint_type = 'R'
and exists(
select 1
from all_constraints
where constraint_name=a.r_constraint_name
and constraint_type in ('P', 'U')
and table_name = :OBJECT_NAME
and owner = :OBJECT_OWNER)
order by table_name, constraint_name]]>
</sql>
</query>
</item>
</items>
Add the extension to SQL Developer:
- Tools > Preferences
- Database > User Defined Extensions
- Click "Add Row" button
- In Type choose "EDITOR", Location is where you saved the xml file above
- Click "Ok" then restart SQL Developer
Navigate to any table and you should now see an additional tab next to SQL one, labelled FK References, which displays the new FK information.
Reference
将扩展添加到 SQL Developer：
- 工具 > 首选项
- 数据库 > 用户定义的扩展
- 单击“添加行”按钮
- 在类型中选择“编辑器”，位置是您保存上面的 xml 文件的位置
- 单击“确定”，然后重新启动 SQL Developer
导航到任何表，您现在应该会在 SQL 旁边看到一个附加选项卡，标记为 FK 引用，它显示新的 FK 信息。
参考
回答by lexu
Replace [Your TABLE] with empin the query below
在下面的查询中将[Your TABLE] 替换为emp
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name='[YOUR TABLE]');
回答by Adam Paynter
You may be able to query this from the ALL_CONSTRAINTSview:
您可以从ALL_CONSTRAINTS视图中查询：
SELECT table_name
FROM ALL_CONSTRAINTS
WHERE constraint_type = 'R' -- "Referential integrity"
AND r_constraint_name IN
( SELECT constraint_name
FROM ALL_CONSTRAINTS
WHERE table_name = 'EMP'
AND constraint_type IN ('U', 'P') -- "Unique" or "Primary key"
);
回答by Mark A. Fitzgerald
SQL Developer 4.1, released in May of 2015, added a Model tab which shows table foreign keys which refer to your table in an Entity Relationship Diagram format.
2015 年 5 月发布的 SQL Developer 4.1 添加了一个模型选项卡，该选项卡显示表外键，这些外键以实体关系图格式引用您的表。
回答by Abu Turab
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 = '&&tableName'
AND column_name = '&&columnName'
CONNECT BY NOCYCLE PRIOR table_name = r_table_name
AND PRIOR column_name = r_column_name;
回答by DCookie
How about something like this:
这样的事情怎么样：
SELECT c.constraint_name, c.constraint_type, c2.constraint_name, c2.constraint_type, c2.table_name
FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
WHERE c.table_name = <TABLE_OF_INTEREST>
AND c.constraint_TYPE = 'R';
回答by thatjeffsmith
This has been in the product for years - although it wasn't in the product in 2011.
这在产品中已经存在多年了——尽管它在 2011 年没有出现在产品中。
But, simply click on the Model page.
但是，只需单击“模型”页面。
Make sure you are on at least version 4.0 (released in 2013) to access this feature.
确保您至少使用 4.0 版（2013 年发布）才能访问此功能。
回答by Srinivasa Raghavan R
To add to the above answer for sql developer plugin, using the below xml will help in getting the column associated with the foreign key.
要添加到 sql developer plugin 的上述答案，使用下面的 xml 将有助于获取与外键关联的列。
<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[FK References]]></title>
<query>
<sql>
<![CDATA[select a.owner,
a.constraint_name,
a.table_name,
b.column_name,
a.status
from all_constraints a
join all_cons_columns b ON b.constraint_name = a.constraint_name
where a.constraint_type = 'R'
and exists(
select 1
from all_constraints
where constraint_name=a.r_constraint_name
and constraint_type in ('P', 'U')
and table_name = :OBJECT_NAME
and owner = :OBJECT_OWNER)
order by table_name, constraint_name]]>
</sql>
</query>
</item>
</items>
