查找特定表的外键的 SQL 脚本? [英] SQL Script to find Foreign keys to a specific table?
问题描述
是否有一个查询可以让我获得指向特定表列的外键?例如,假设我有这三个表:
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)
-----------
我需要按照选择 * 指向 TableA.Id 的外键"行的查询,返回表 C:TableAId"、表 B:TableAId".我正在浏览一些 INFORMATION_SCHEMA 系统视图,似乎我可以很容易地看到哪些外键分别属于表 A 或表 B,但我找不到它说表 C 有一个外键表 A".我可以弄清楚查询的细节,我只是找不到我正在寻找的视图(或者我在掩饰它们).任何帮助将不胜感激.
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.
推荐答案
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
这篇关于查找特定表的外键的 SQL 脚本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!