针对 SqlClient 的 getschema("foreignkeys") 没有产生足够的信息

Question

我需要两个表和两组字段，而不是外键名称和其中一个表名称。有谁知道如何查询SQL Server完整的外键信息？谢谢！

Answer 1

这可能是一项复杂的冒险。 GetSchema 以及 INFORMATION_SCHEMA 视图不完整，导致需要直接查询 sys 视图以获取权威信息。

尝试针对您的数据库运行此命令并了解您可以做什么。

这是来自失控的思想实验;-)

 BEGIN -- Get a table full of PK and UQ columns
        DECLARE @tbl_unique_key_columns TABLE ( -- contains PK and UQ indexes
                                                table_schema NVARCHAR(128),
                                                table_name NVARCHAR(128),
                                                index_name NVARCHAR(128),
                                                column_id INT,
                                                column_name NVARCHAR(128),
                                                is_primary_key BIT,
                                                is_unique_constraint BIT,
                                                is_unique BIT,
                                                is_nullable BIT,
                                                is_rowguidcol BIT,
                                                is_identity BIT,
                                                default_definition NVARCHAR(MAX),
                                                user_type NVARCHAR(128),
                                                table_object_id INT )
        INSERT  INTO @tbl_unique_key_columns ( table_schema, table_name, index_name, column_id, column_name, is_primary_key, is_unique_constraint, is_unique, is_nullable, is_rowguidcol, is_identity, default_definition, user_type, table_object_id )

        -- selects PK and UQ indexes
                SELECT  S.name AS schema_name, T.name AS table_name, IX.name AS index_name, IC.column_id, C.name AS column_name, IX.is_primary_key, IX.is_unique_constraint, IX.is_unique, C.is_nullable, C.is_rowguidcol, C.is_identity, d.definition, tp.NAME, T.[object_id]
                FROM    sys.tables AS T
                INNER JOIN sys.schemas AS S
                ON      T.schema_id = S.schema_id
                INNER JOIN sys.indexes AS IX
                ON      T.object_id = IX.object_id
                INNER JOIN sys.index_columns AS IC
                ON      IX.object_id = IC.object_id
                        AND IX.index_id = IC.index_id
                INNER JOIN sys.columns AS C
                ON      IC.column_id = C.column_id
                        AND IC.object_id = C.OBJECT_ID
                INNER JOIN sys.types AS tp
                ON      C.user_type_id = tp.user_type_id
                LEFT OUTER JOIN sys.default_constraints AS d
                ON      T.object_id = d.parent_object_id
                        AND C.column_id = d.parent_column_id
                WHERE   ( IX.is_unique = 1 )
                        AND ( IX.is_unique = 1 )
                ORDER BY schema_name, table_name, index_name, C.column_id
    END

    BEGIN -- Get a table full of FK columns

        DECLARE @tbl_foreign_key_columns TABLE ( constraint_name NVARCHAR(128),
                                                 base_schema_name NVARCHAR(128),
                                                 base_table_name NVARCHAR(128),
                                                 base_column_id INT,
                                                 base_column_name NVARCHAR(128),
                                                 unique_schema_name NVARCHAR(128),
                                                 unique_table_name NVARCHAR(128),
                                                 unique_column_id INT,
                                                 unique_column_name NVARCHAR(128),
                                                 base_object_id INT,
                                                 unique_object_id INT )
        INSERT  INTO @tbl_foreign_key_columns ( constraint_name, base_schema_name, base_table_name, base_column_id, base_column_name, unique_schema_name, unique_table_name, unique_column_id, unique_column_name, base_object_id, unique_object_id )
                SELECT  FK.name AS constraint_name, S.name AS base_schema_name, T.name AS base_table_name, C.column_id AS base_column_id, C.name AS base_column_name, US.name AS unique_schema_name, UT.name AS unique_table_name, UC.column_id AS unique_column_id, UC.name AS unique_column_name, T.[object_id], UT.[object_id]
                FROM    sys.tables AS T
                INNER JOIN sys.schemas AS S
                ON      T.schema_id = S.schema_id
                INNER JOIN sys.foreign_keys AS FK
                ON      T.object_id = FK.parent_object_id
                INNER JOIN sys.foreign_key_columns AS FKC
                ON      FK.object_id = FKC.constraint_object_id
                INNER JOIN sys.columns AS C
                ON      FKC.parent_object_id = C.object_id
                        AND FKC.parent_column_id = C.column_id
                INNER JOIN sys.columns AS UC
                ON      FKC.referenced_object_id = UC.object_id
                        AND FKC.referenced_column_id = UC.column_id
                INNER JOIN sys.tables AS UT
                ON      FKC.referenced_object_id = UT.object_id
                INNER JOIN sys.schemas AS US
                ON      UT.schema_id = US.schema_id
                ORDER BY base_schema_name, base_table_name
    END

SELECT * FROM @tbl_unique_key_columns
SELECT * from @tbl_foreign_key_columns