如何在 Oracle SQL Developer 中找到引用给定表的表?

How can I find which tables reference a given table in Oracle SQL Developer?(如何在 Oracle SQL Developer 中找到引用给定表的表?)

问题描述

在 Oracle SQL Developer 中，如果我正在查看表上的信息，我可以查看约束，这让我可以看到外键(因此该表引用了哪些表)，并且我可以查看依赖项以查看哪些包和此类引用该表.但我不确定如何找到哪些表引用了该表.

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.

例如，假设我正在查看 emp 表.还有另外一张表emp_dept，它记录了哪些员工在哪些部门工作，通过emp_id引用emp表，emp 表.有没有办法(通过程序中的一些 UI 元素，而不是通过 SQL)找到 emp_dept 表引用 emp 表，而我不必知道 emp_dept 表存在吗?

For example, say I'm looking at the emp table. There is another table emp_dept which captures which employees work in which departments, which references the emp table through emp_id, the primary key of the emp table. Is there a way (through some UI element in the program, not through SQL) to find that the emp_dept table references the emp table, without me having to know that the emp_dept table exists?

推荐答案

没有.Oracle SQL Developer 没有提供这样的选项.

No. There is no such option available from Oracle SQL Developer.

您必须手动执行查询或使用其他工具(例如 PLSQL Developer这样的选择).以下 SQL 是 PLSQL Developer 使用的 SQL:

You have to execute a query by hand or use other tool (For instance PLSQL Developer has such option). The following SQL is that one used by PLSQL Developer:

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

其中 r_owner 是架构，r_table_name 是您要查找参考的表.名称区分大小写

Where r_owner is the schema, and r_table_name is the table for which you are looking for references. The names are case sensitive

要小心，因为在 Oracle SQL Developer 的报告选项卡上有所有表/依赖项"选项，这是来自 ALL_DEPENDENCIES 指当前用户可访问的过程、包、函数、包主体和触发器之间的依赖关系，包括对在没有任何数据库链接的情况下创建的视图.".那么，这份报告对您的问题没有任何价值.

Be careful because on the reports tab of Oracle SQL Developer there is the option "All tables / Dependencies" this is from ALL_DEPENDENCIES which 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 中找到引用给定表的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！