用于查找特定表的外键的 SQL 脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7853187/
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
SQL Script to find Foreign keys to a specific table?
提问by Ocelot20
Is there a query that will get me foreign keys directed at a specific table column? For example, say I had these three tables:
是否有一个查询可以让我获得指向特定表列的外键？例如，假设我有这三个表：
__________
|Table A |
----------
|Id |
----------
___________
|Table B |
-----------
|Id |
|TableAId | (Foreign Key to TableA.Id)
-----------
___________
|Table C |
-----------
|Id |
|TableAId | (Foreign Key to TableA.Id)
-----------
I need a query along the lines of "Select * Foreign Keys directed at TableA.Id" that returned "Table C: TableAId", "Table B: TableAId". I'm browsing through some of the INFORMATION_SCHEMA system views, and it seems like I can easily see what foreign keys belong to Table A, or Table B individually, but I can't find where it says "Table C has a foreign key to Table A" specifically. I can figure out the specifics of the query, I just can't find the views I'm looking for (or I'm glossing over them). Any help would be appreciated.
我需要沿着“选择 * 指向 TableA.Id 的外键”行进行查询，该查询返回“表 C：TableAId”、“表 B：TableAId”。我正在浏览一些 INFORMATION_SCHEMA 系统视图，似乎我可以很容易地看到哪些外键属于表 A 或表 B，但我找不到它说“表 C 有一个外键表A”。我可以弄清楚查询的细节，我只是找不到我正在寻找的视图（或者我正在掩盖它们）。任何帮助，将不胜感激。
回答by Michael Fredrickson
SELECT
f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
回答by Tomer Bar-Shlomo
Added dynamic drop and create on dbo scheme.
在 dbo 方案上添加了动态删除和创建。
DECLARE @l varchar(1) = char(13);
DECLARE @referenced_object_id varchar(MAX) = 'Replace_With_PK_Table_Name';
SELECT
fk.name AS ForeignKey,
OBJECT_NAME(fk.parent_object_id) AS TableName,
COL_NAME(fkc.parent_object_id,
fkc.parent_column_id) AS ColumnName,
OBJECT_NAME (fk.referenced_object_id) AS ReferenceTableName,
COL_NAME(fkc.referenced_object_id,
fkc.referenced_column_id) AS ReferenceColumnName,
'ALTER TABLE [dbo].['+OBJECT_NAME(fk.parent_object_id)+'] DROP CONSTRAINT ['+fk.name+'];' DropFK,
'ALTER TABLE [dbo].['+OBJECT_NAME(fk.parent_object_id)+'] WITH NOCHECK ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY(['+COL_NAME(fkc.referenced_object_id,
fkc.referenced_column_id)+'])'+ @l +
' REFERENCES [dbo].['+OBJECT_NAME(fk.referenced_object_id)+'] (['+COL_NAME(fkc.referenced_object_id,
fkc.referenced_column_id)+'])'+ @l +
' ON UPDATE CASCADE'+ @l +
' ON DELETE CASCADE;'+ @l +
'ALTER TABLE [dbo].['+OBJECT_NAME(fk.parent_object_id)+'] CHECK CONSTRAINT ['+fk.name+'];' CreateFK
FROM
sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc ON fk.OBJECT_ID = fkc.constraint_object_id
WHERE OBJECT_NAME(fk.referenced_object_id)=@referenced_object_id
