getschema("foreignkeys") against SqlClient doesn't yield enough information

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!

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.

This is from a thought experiment that got out of hand ;-)

 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