SQL脚本来查找特定表的外键？

本文介绍了SQL脚本来查找特定表的外键？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有没有查询会让我的外键指向一个特定的表列？例如，假设我有这三个表：

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)
-----------

我需要一个查询返回Table C：TableAId，Table B：TableAId的Select * Foreign Keys directed at TableA.Id行。我正在浏览一些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脚本来查找特定表的外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！