我如何才能找到哪些表在Oracle SQL Developer中引用给定的表？ [英] How can I find which tables reference a given table in Oracle SQL Developer?

问题描述

在 Oracle SQL Developer 中，如果我正在查看在表上的信息，我可以查看约束，这让我看到外键（以及由此表引用哪些表），我可以查看依赖关系，看看什么包和这样的引用表。但我不知道如何找到哪个表引用表。

例如，我正在查看 emp 表。还有另一个表 emp_dept ，它捕获哪些员工在哪些部门工作，哪些部门通过<$ c $引用 emp 表c> emp_id ， emp 表的主键。有没有办法（通过程序中的一些UI元素，而不是通过SQL）发现 emp_dept 表引用了 emp 表，而不必知道 emp_dept 表存在吗？

解决方案

没有。 Oracle SQL Developer没有这样的选项。

您必须手动执行查询或使用其他工具（例如 PLSQL Developer 有这样的选项）。以下SQL是由PLSQL Developer使用的：

  select table_name，constraint_name，status，owner $ b $ from all_constraints 
其中r_owner =：r_owner 
和constraint_type ='R'
和
中的r_constraint_name（
从all_constraints $中选择constraint_name 
其中constraint_type in（'P'， 'U'）
和table_name =：r_table_name 
和owner =：r_owner 
）
按表名称，constraint_name 
  

$ b $ r_owner 是架构，而 r_table_name 是您正在寻找参考的表格。名称是区分大小写的

---

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

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.

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?

解决方案

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

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

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

---

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中引用给定的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！