我可以帮您翻译成中文：在哪里可以验证SQL关键类型代码？

Question

根据SQL Server Schema Collections，我想使用sqlConnection.GetSchema("IndexColumns");来确定哪些列是主键。

我进行了一些实验，并发现通过上述请求，我可以得到一个包含以下信息的数据表：

table_catalog   table_schema   table_name              column_name ordinal_position   KeyType   index_name
---------------------------------------------------------------------------------------------------------------------------
TestDb          dbo            TestTableWithPrimaryKey     Id              1             56      PK_TestTableWithPrimaryKey 

我猜测KeyType = 56代表着主键，但这仅是通过该表定义推断出来的：

CREATE TABLE [dbo].[TestTableWithPrimaryKey]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Foo] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_TestTableWithPrimaryKey] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]

在实际使用这个发现之前，我想确认它是正确的。有没有可以获取所有关键类型的地方？

- t3chb0t

Answer 1

KeyType列似乎与SQL Server sys.types目录视图中的system_type列枚举的列数据类型相对应（值56为int）。因此，这不能帮助识别该列是否为主键成员。

有多种方法可以获取SQL Server主键列，包括SMO和目录视图查询。以下是一个示例，用于获取SQL Server数据库中所有主键列。如果您需要支持多个DBMS产品，则可以使用实现了那些ANSI标准视图的DBMS产品的INFORMATION_SCHEMA目录视图。

SELECT 
      OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName
    , OBJECT_NAME(i.object_id) AS TableName
    , c.name AS ColumnName
    , ic.key_ordinal AS KeyOrdinal
FROM sys.key_constraints AS kc
JOIN sys.indexes AS i ON i.object_id = kc.parent_object_id AND kc.name = i.name
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE kc.type_desc = N'PRIMARY_KEY_CONSTRAINT'
ORDER BY
      SchemaName
    , TableName
    , KeyOrdinal;

- Dan Guzman

Answer 2

通过查看内部的SqlMetaDataFactory，调用sqlConnection.GetSchema("IndexColumns");会查找在资源文件System.Data.SqlClient.SqlMetaData.xml中找到的要执行的命令，并且这将显示我们的条目IndexColumns。

 <MetaDataCollections>
    <CollectionName>IndexColumns</CollectionName>
    <NumberOfRestrictions>5</NumberOfRestrictions>
    <NumberOfIdentifierParts>4</NumberOfIdentifierParts>
    <PopulationMechanism>SQLCommand</PopulationMechanism>
    <PopulationString>EXEC sys.sp_indexcolumns_managed @Catalog, @Owner, @Table, @ConstraintName, @Column</PopulationString>
    <MinimumVersion>10.00.0000</MinimumVersion>
  </MetaDataCollections>

这意味着它将执行EXEC sys.sp_indexcolumns_managed。该存储过程将通过EXEC sp_helptext 'sys.sp_indexcolumns_managed'显示，它从sys.spt_indexcolumns_view_managed中选择，并且该视图被定义为：

select distinct 
    db_Name() as constraint_catalog, 
    constraint_schema = SCHEMA_NAME(o.schema_id), 
    constraint_name = x.name, 
    table_catalog  = db_name(), 
    table_schema = SCHEMA_NAME(o.schema_id), 
    table_name = o.name, 
    column_name = c.name, 
    ordinal_position = xc.key_ordinal, 
    KeyType  = c.system_type_id, 
    index_name = x.name 
from 
    sys.objects o INNER JOIN sys.indexes x ON
        (
            o.object_id = x.object_id AND
            o.type in ('U')
        )  INNER JOIN 
    sys.index_columns xc ON
        (
            xc.object_id = x.object_id AND
            xc.index_id = x.index_id
        ) INNER JOIN
    sys.columns c ON
        (
            o.object_id = c.object_id AND
            xc.column_id = c.column_id
        )

(您可以使用 SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sp_indexcolumns_managed')); 查找其定义，因为它是资源数据库的一部分)

现在我们可以看到，KeyType列是基于sys.columns.system_type_id投影的，该信息在sys.columns中有记录：

列的系统类型ID。

而在哪里可以找到列数据类型的Sql Server元数据？则详细说明了与sys.types连接将为您提供列类型的信息。

有了这些信息，我们已经可以得出结论：我猜KeyType = 56代表主键不是正确的，并且运行

select name, system_type_id, user_type_id, schema_id, max_length,  precision scale
from sys.types 
where system_type_id = 56

将返回

name system_type_id user_type_id schema_id max_length scale 
---- -------------- ------------ --------- ---------- ----- 
int  56             56           4         4          10    

而这是列类型，而不是是否为主键。

似乎没有可靠的方法通过 GetSchema 调用找到主键，据我所知。请使用 Dan Guzman 提供的查询。

- rene

---

这真是一项惊人的侦探工作。我不太懂 SQL，所以我不可能做到这么好——而且我从你的答案中学到了很多。很遗憾他们选择了一个如此令人困惑的名称_KeyType_，在其他地方它被正确地称为_DataType_。我会接受这个答案，因为它深入到我想要验证的魔数“56”的底部，并展示了一些新的工具，让我将来可以自己完成这个任务——虽然作为备选方案，我将不得不转向@DanGuzman建议的方法，因为我的理论被证明是错误的。 - t3chb0t