如何获取表的主键列(对于组合主键)

I have a table that has composite primary key.

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

I am doing an update on that table and in the where clause I need the name of primary columns. Till now I have been using:

我正在对该表进行更新，在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 + "'";

To get the primary key column. However it fails where the primary key is composite. How do I do the update here? Help.

获取主键列。然而，当主键是复合键时，它就失败了。如何在这里进行更新?的帮助。

4 个解决方案

#1

The following query will get all primary key columns:

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

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

示例SQL小提琴

As has been noted in a comment you should be using parameterised queries.

正如评论中提到的，您应该使用参数化查询。

#2

Use the command objects for a pass values as parameters to SQL statements, providing type checking and validation

将传递值的命令对象用作SQL语句的参数，提供类型检查和验证

using (SqlConnection conn = new SqlConnection(sqlBuilder.ConnectionString))
{
      string schemaName = "yourSchemaName";
      string tableName = "yourTableName";
      SqlCommand command = new SqlCommand(@"
        SELECT column_name
        FROM information_schema.key_column_usage
        WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @tableName
          AND OBJECTPROPERTY(object_id(constraint_name), 'IsPrimaryKey') = 1
        ORDER BY table_schema, table_name", conn);
      command.Parameters.Add("@schemaName", SqlDbType.VarChar, 100).Value = schemaName;
      command.Parameters.Add("@tableName", SqlDbType.VarChar, 100).Value = tableName;
      conn.Open();                
      SqlDataReader reader = command.ExecuteReader();
      try
      {                    
          while (reader.Read())
          {
              Console.WriteLine(reader[0]);
          }
      }

      finally
      {
          reader.Close();
      }

 }

#3

SELECT the table name And press Alt+F1 You will get all the details required .

选择表名并按Alt+F1，您将获得所需的所有细节。

#4

Your query seems to be just fine. I’ve tested it and it returned all columns primary key is consisted of.

您的查询似乎很好。我对它进行了测试，它返回了所有列的主键。

Try running this in SSMS and you’ll get two

试试在SSMS中运行这个，你会得到两个

CREATE TABLE dbo.T1
(
    ID int NOT NULL,
    SomeTextColumn nvarchar(30) NOT NULL,
    SomeColumn nvarchar(20),
    SomeOtherColumn uniqueidentifier,
    CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED (ID, SomeTextColumn)
)


 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 = 'T1'

Here is a SQL Fiddle example for this.

这里有一个SQL Fiddle示例。

If you need to filter this by column you can always add “and col.COLUMN_NAME = 'ID'”

如果需要按列过滤，可以添加" and col.COLUMN_NAME = 'ID' "

#1