.net 如何在 SQL Server 中查找外键依赖项？

How to find foreign key dependencies in SQL Server?

提问by Even Mien

How can I find all of the foreign key dependencies on a particular column?

如何找到特定列上的所有外键依赖项？

What are the different alternatives (graphically in SSMS, queries/views in SQL Server, 3rd party database tools, code in .NET)?

有哪些不同的替代方案（SSMS 中的图形、SQL Server 中的查询/视图、第 3 方数据库工具、.NET 中的代码）？

回答by John Sansom

The following query will help to get you started. It lists all Foreign Key Relationships within the current database.

以下查询将帮助您入门。它列出了当前数据库中的所有外键关系。

SELECT
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.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

You can also view relationships graphically within SQL Server Management studio within Database Diagrams.

您还可以在 SQL Server Management Studio 中的数据库图表中以图形方式查看关系。

回答by Andrija

try: sp_help [table_name]

尝试： sp_help [table_name]

you will get all information about table, including all foreign keys

您将获得有关表的所有信息，包括所有外键

回答by Timothy Walden

If you plan on deleting or renaming a table or column finding only foreign key dependencies might not be enough.

如果您计划删除或重命名表或列，仅查找外键依赖项可能是不够的。

Referencing tables not connected with foreign key- You'll also need to search for referencing tables that might not be connected with foreign key (I've seen many databases with bad design that didn't have foreign keys defined but that did have related data). Solution might be to search for column name in all tables and look for similar columns.

引用未与外键连接的表- 您还需要搜索可能未与外键连接的引用表（我见过许多设计不佳的数据库，它们没有定义外键但确实有相关数据）。解决方案可能是在所有表中搜索列名并查找相似的列。

Other database objects– this is probably a bit off topic but if you were looking for all references than it's also important to check for dependent objects.

其他数据库对象——这可能有点偏离主题，但如果您正在寻找所有引用，那么检查相关对象也很重要。

GUI Tools – Try SSMS “Find related objects” option or tools such as ApexSQL Search(free tool, integrates into SSMS) to identify all dependent objects including tables connected with foreign key.

GUI 工具 - 尝试使用 SSMS“查找相关对象”选项或ApexSQL Search（免费工具，集成到 SSMS）等工具来识别所有依赖对象，包括与外键连接的表。

回答by Michael

Because your question is geared towards a single table, you can use this:

因为您的问题是针对单个表的，所以您可以使用这个：

EXEC sp_fkeys 'TableName'

I found it on SO here:

我在这里找到它：

https://stackoverflow.com/a/12956348/652519

https://stackoverflow.com/a/12956348/652519

I found the information I needed pretty quickly. It lists the foreign key's table, column and name.

我很快就找到了我需要的信息。它列出了外键的表、列和名称。

EDIT

编辑

Here's a link to the documentation that details the different parameters that can be used: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

这是详细说明可以使用的不同参数的文档链接：https: //docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

回答by Michael

I think this script is less expensive:

我认为这个脚本更便宜：

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 TheTXI

One that I really like to use is called SQL Dependency Tracker by Red Gate Software. You can put in any database object(s) such as tables, stored procedures, etc. and it will then automatically draw the relationship lines between all the other objects that rely on your selected item(s).

我非常喜欢使用的一种称为Red Gate Software 的SQL Dependency Tracker 。您可以放入任何数据库对象，例如表、存储过程等，然后它会自动绘制依赖于您选择的项目的所有其他对象之间的关系线。

Gives a very good graphical representation of the dependencies in your schema.

为您的架构中的依赖关系提供了非常好的图形表示。

回答by Sierramike

Thanks so much to John Sansom, his query is terrific !

非常感谢 John Sansom，他的问题很棒！

In addition : you should add " AND PT.ORDINAL_POSITION = CU.ORDINAL_POSITION" at the end of your query.

另外：您应该在查询的末尾添加“AND PT.ORDINAL_POSITION = CU.ORDINAL_POSITION”。

If you have multiple fields in primary key, this statement will match the corresponding fields to each other (I had the case, your query did create all combinations, so for 2 fields in primary key, I had 4 results for the corresponding foreign key).

如果主键中有多个字段，则此语句会将相应的字段相互匹配（我遇到过这种情况，您的查询确实创建了所有组合，因此对于主键中的 2 个字段，对应的外键有 4 个结果） .

(Sorry I can't comment John's answer as I don't have enough reputation points).

（对不起，我不能评论约翰的回答，因为我没有足够的声望点）。

回答by Kumait

This query will return details about foreign keys in a table, it supports multiple column keys.

此查询将返回有关表中外键的详细信息，它支持多列键。

    SELECT *
    FROM
    (
    SELECT 
    T1.constraint_name ConstraintName,
    T2.COLUMN_NAME ColumnName,
    T3.TABLE_NAME RefTableName, 
    T3.COLUMN_NAME RefColumnName,
    T1.MATCH_OPTION MatchOption, 
    T1.UPDATE_RULE UpdateRule, 
    T1.DELETE_RULE DeleteRule
    FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T1
    INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE T2 
    ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
    INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE T3 
    ON T1.UNIQUE_CONSTRAINT_NAME = T3.CONSTRAINT_NAME 
    AND T2.ORDINAL_POSITION = T3.ORDINAL_POSITION) A
    WHERE A.ConstraintName = 'table_name'

回答by profimedica

After long search I found a working solution. My database does not use the sys.foreign_key_columns and the information_schema.key_column_usage only contain primary keys.

经过长时间的搜索，我找到了一个可行的解决方案。我的数据库不使用 sys.foreign_key_columns 并且 information_schema.key_column_usage 只包含主键。

I use SQL Server 2015

我使用 SQL Server 2015

SOLUTION 1 (rarely used)

解决方案 1（很少使用）

If other solutions does not work, this will work fine:

如果其他解决方案不起作用，这将正常工作：

        WITH CTE AS
        (
            SELECT 
                TAB.schema_id,
                TAB.name,
                COL.name AS COLNAME,
                COl.is_identity
            FROM 
                sys.tables TAB INNER JOIN sys.columns COL 
                    ON TAB.object_id = COL.object_id
        )
        SELECT 
            DB_NAME() AS [Database], 
            SCHEMA_NAME(Child.schema_id) AS 'Schema',
            Child.name AS 'ChildTable',
            Child.COLNAME AS 'ChildColumn',
            Parent.name AS 'ParentTable',
            Parent.COLNAME AS 'ParentColumn'
        FROM 
            cte Child INNER JOIN CTE Parent
                ON 
                    Child.COLNAME=Parent.COLNAME AND 
                    Child.name<>Parent.name AND 
                    Child.is_identity+1=Parent.is_identity

SOLUTION 2 (commonly used)

解决方案2（常用）

In most of the cases this will work just fine:

在大多数情况下，这将正常工作：

        SELECT
            DB_NAME() AS [Database], 
            SCHEMA_NAME(fk.schema_id) AS 'Schema',
            fk.name 'Name',
            tp.name 'ParentTable',
            cp.name 'ParentColumn',
            cp.column_id,
            tr.name 'ChildTable',
            cr.name 'ChildColumn',
            cr.column_id
        FROM
            sys.foreign_keys fk
        INNER JOIN
            sys.tables tp ON fk.parent_object_id = tp.object_id
        INNER JOIN
            sys.tables tr ON fk.referenced_object_id = tr.object_id
        INNER JOIN
            sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        INNER JOIN
            sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
        INNER JOIN
            sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
        WHERE 
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tp.name, '.', cp.name) LIKE '%my_table_name%' OR
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tr.name, '.', cr.name) LIKE '%my_table_name%' 
        ORDER BY
            tp.name, cp.column_id

回答by Shreyansh Bothra

You can Use INFORMATION_SCHEMA.KEY_COLUMN_USAGE and sys.foreign_key_columns in order to get the foreign key metadata for a table i.e. Constraint name, Reference table and Reference column etc.

您可以使用 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 和 sys.foreign_key_columns 来获取表的外键元数据，即约束名称、引用表和引用列等。

Below is the query:

下面是查询：

SELECT  CONSTRAINT_NAME, COLUMN_NAME, ParentTableName, RefTableName,RefColName FROM 
    (SELECT CONSTRAINT_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '<tableName>') constraint_details
    INNER?JOIN? 
    (SELECT ParentTableName, RefTableName,name ,COL_NAME(fc.referenced_object_id,fc.referenced_column_id) RefColName  FROM (SELECT object_name(parent_object_id) ParentTableName,object_name(referenced_object_id) RefTableName,name,OBJECT_ID? FROM sys.foreign_keys WHERE parent_object_id = object_id('<tableName>') ) f 
    INNER?JOIN?  
    sys.foreign_key_columns?AS?fc? ON? f.OBJECT_ID?=?fc.constraint_object_id ) foreign_key_detail 
    on foreign_key_detail.name = constraint_details.CONSTRAINT_NAME