SQL 在引用某个主键的数据库中查找所有外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20968965/
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
Find all foreign keys constraints in database referencing a certain primary key
提问by Hamid Talebi
I want to find all foreign keys in my database that reference to a primary key of a certain table.
我想在我的数据库中找到所有引用某个表的主键的外键。
For example, I have a column Ain table Twhich is the primary key. Now I want to find in which tables column Ais referenced in a foreign key constraint?
例如，我A在表中有一个列T是主键。现在我想查找A外键约束中引用了哪些表列？
One simple way I've considered is to check the database diagram, but this only works if a database is very small. It's not a very good solution for a database that has more than 50 tables.
我考虑过的一种简单方法是检查数据库图表，但这仅适用于数据库非常小的情况。对于超过 50 个表的数据库来说，这不是一个很好的解决方案。
Any alternatives?
任何替代方案？
采纳答案by Thomas B. Lze
Look at How to find foreign key dependencies in SQL Server?
You can sort on PK_Table and PK_Column to get what you want
您可以对 PK_Table 和 PK_Column 进行排序以获得您想要的
回答by Premchand Yelavarthi
On the last line, change [Primary Key Table] to your table name, change [Primary Key Column] to your column name, and execute this script on your database to get the foreign keys for the primary key.
在最后一行，将[主键表]更改为您的表名，将[主键列]更改为您的列名，并在您的数据库上执行此脚本以获取主键的外键。
SELECT FK.TABLE_NAME as Key_Table,CU.COLUMN_NAME as Foreignkey_Column,
PK.TABLE_NAME as Primarykey_Table,
PT.COLUMN_NAME as Primarykey_Column,
C.CONSTRAINT_NAME as Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME =Fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME=PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME =i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME = '[Primary Key Table]' and PT.COLUMN_NAME = '[Primary Key Column]';
回答by oerkelens
You want to query the sys tables.
您想查询 sys 表。
The query to get all table names where your column is used as a fk would be something like
获取列用作 fk 的所有表名的查询类似于
select name
from sys.tables
where object_id in
( select parent_object_id
from sys.foreign_key_columns
where referenced_object_id = 12345
and referenced_column_id = 1);
To get your referenced_object_id and referenced_column id:
要获取您的referenced_object_id 和referenced_column id：
select object_id from sys.tables where name = 'Table T'
With that object_id, find column id:
使用该 object_id，找到列 id：
select column_id from sys.columns where name = 'Column A' and object_id = 12345
