SQL 查找给定表的所有相关表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2540315/
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
Finding all related tables to a given table
提问by gillian
I'm working on a development with over 700 tables. I need to see a listing of all related tables to a given table. Can you recommend an app that is able to provide such a thing.
我正在开发一个有 700 多个表的开发项目。我需要查看给定表的所有相关表的列表。你能推荐一个能够提供这样的东西的应用程序吗？
回答by Aaronaught
If your database supports the information schema views (most do), then you can run this query:
如果您的数据库支持信息架构视图（大多数支持），那么您可以运行以下查询：
SELECT
c.CONSTRAINT_NAME,
cu.TABLE_NAME AS ReferencingTable, cu.COLUMN_NAME AS ReferencingColumn,
ku.TABLE_NAME AS ReferencedTable, ku.COLUMN_NAME AS ReferencedColumn
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
ON cu.CONSTRAINT_NAME = c.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON ku.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
This will output a list of all referential constraints (foreign keys), the source (referencing) table/column, and primary key (referenced) table/column.
这将输出所有引用约束（外键）、源（引用）表/列和主键（引用）表/列的列表。
If you want to see references to a specifictable, just add:
如果您想查看对特定表的引用，只需添加：
WHERE ku.TABLE_NAME = 'SomeTable'
回答by Dipak Delvadiya
Simple and easiest solution to get the related tables of given table using inbuilt procedure.
使用内置程序获取给定表的相关表的简单和最简单的解决方案。
exec sp_fkeys 'Your_PK_TABLE_NAME'
回答by Thomas
Depending on the database product, you should be able to query the INFORMATION_SCHEMAviews like so:
根据数据库产品，您应该能够INFORMATION_SCHEMA像这样查询视图：
Select FK.TABLE_SCHEMA, FK.TABLE_NAME
From INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS As RC
Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As PK
On PK.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As FK
On FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
Where PK.TABLE_SCHEMA = 'dbo'
And PK.TABLE_NAME = '<target table name>'
回答by Paul Sasik
MS Visio Procan do a good job of reverse engineering a db schema for a variety of databases (via ODBC) and provides a diagram that is very flexible.
MS Visio Pro可以很好地为各种数据库（通过 ODBC）逆向工程 db 模式，并提供非常灵活的图表。
Word of caution: Regardless of what system you use you do not want to pull in all 700 tables all at once. Try to create several diagram grouped and separated logically. Hopefully there will be many such groupings in a database that large.
警告：无论您使用什么系统，您都不想一次拉入所有 700 个表。尝试创建多个逻辑分组和分隔的图表。希望在这么大的数据库中会有很多这样的分组。
回答by Eduard Streltsov
I use DataGrip diagrams with "Key columns" enabled and just "Columns" disabled. It's not a list of course, and I can't imagine how it looks like on a DB with 700 tables, but it's the most convenient way for me to see all relations between tables in my project.
我使用启用了“关键列”而仅禁用“列”的 DataGrip 图。这当然不是一个列表，我无法想象它在一个有 700 个表的数据库上的样子，但它是我查看项目中表之间所有关系的最方便的方式。
