如何在 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 中的查询/视图、第 3 方数据库工具、.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
    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

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

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

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