Oracle所有外键参考 [英] Oracle all foreign key references

问题描述

我继承了一个架构，因此对其中的内容没有完全的了解/信心.

I've inherited a schema so don't have complete knowledge/confidence in what is there.

我有一个带有projectId的项目表.还有许多其他表按项目ID引用此表.我想做的是运行一个查询来建立:

I have a project table with a projectId. There are a whole bunch of other tables that reference this table by project id. What I want to do is run a query to establish:

1. 哪些表在表上具有对项目表的外键引用 项目ID
2. 哪些表中有一个称为项目ID的列(在 如果未设置外键).

1. Which tables have foreign key references to the project table on the project id
2. Which tables have a column called project id (in case foreign keys are not set up).

如果是SQL Server，我知道如何查询元数据，但是如何在Oracle中做到这一点?

If it was SQL Server I know how to query the metadata but how do I do this in Oracle?

推荐答案

1)

select table_name
  from all_constraints
 where r_constraint_name = [your pk/uk constraint on PROJECTS(id)]

2)

select table_name
  from all_tab_columns
 where column_name = 'PROJECT_ID'

您可能想添加一个包含OWNER列的额外谓词.

You may want to add an extra predicate containing the OWNER column.

关于， 罗布.

这篇关于Oracle所有外键参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！