的getSchema（＆QUOT; foreignkeys＆QUOT;）对SqlClient中不会产生足够的信息 [英] getschema("foreignkeys") against SqlClient doesn't yield enough information

问题描述

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

I need two tables and two sets of fields, not the name of the foreign key and one of the table names. Does anyone know how to query SQL Server for complete foreign key information? Thanks!

推荐答案

这可能是一个复杂的企业。该的getSchema以及INFORMATION_SCHEMA视图不完整，导致需要直接查询SYS意见的权威的信息。

This can be an involved venture. The GetSchema as well as INFORMATION_SCHEMA views are incomplete, leading to the need to query the sys views directly for authoritive info.

尝试对你的数据库运行此，了解一下就可以了。

try running this against your db and learn what you can.

这是从思想实验是一发不可收拾; - ）

 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

这篇关于的getSchema（＆QUOT; foreignkeys＆QUOT;）对SqlClient中不会产生足够的信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！