c# - 如何获取表的 PRIMARY KEY COLUMNS(如果是 COMPOSITE 主键)

Question

我有一个包含复合主键的表。

我正在对该表进行更新，在 where 子句中我需要主列的名称。到目前为止，我一直在使用:

 string sql = "SELECT PrimaryColumnName = col.column_name
               FROM information_schema.table_constraints tc
                 INNER JOIN information_schema.key_column_usage col
                   ON col.Constraint_Name = tc.Constraint_Name 
                     AND col.Constraint_schema = tc.Constraint_schema
               WHERE tc.Constraint_Type = 'Primary Key'
                 AND col.Table_name = '" + TABLE NAME HERE + "'";

获取主键列。但是，在主键是复合键的情况下它会失败。我如何在这里进行更新？帮助。

Answer 1

以下查询将获取所有主键列:

SELECT  c.Name
FROM    [sys].[index_columns] ic
        INNER JOIN [sys].[columns] c
            ON ic.[object_id] = c.[object_id]
            AND ic.[column_id] = c.[column_id]
        INNER JOIN [sys].[indexes] i
            ON i.[object_id] = ic.[object_id]
            AND i.[index_id] = ic.[index_id]
WHERE   i.is_primary_key = 1
AND     ic.[object_id] = OBJECT_ID(@ObjectName);

Example on SQL Fiddle

如评论中所述，您应该使用参数化查询。