如何找到在SQL Server中的外键的依赖？ [英] How to find foreign key dependencies in SQL Server?

问题描述

我如何可以找到所有的外键依赖于特定的列？

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

有什么不同的选择（图形在SSMS中，查询/视图在SQL Server中，第三方数据库工具，code。在.NET）？

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

推荐答案

下面的查询将有助于让你开始。它列出了所有的外键关系当前数据库中。

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

SELECT
    K_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

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

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

这篇关于如何找到在SQL Server中的外键的依赖？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！