找到一个特定表的外键的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)
-----------

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 * Foreign Keys指向TableA”的查询。Id" that return "Table C: TableAId" "Table B: TableAId"我浏览了一些INFORMATION_SCHEMA系统视图，似乎可以很容易地看到表A的外键，或者表B的单独的外键，但是我找不到表C有外键到表A的具体位置。我可以找出查询的细节，只是找不到我要查找的视图(或者我在粉饰它们)。如有任何帮助，我们将不胜感激。

2 个解决方案

#1

Courtesy of Pinal Dave:

由Pinal Dave:

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

#2

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

#1