CODEWITHSUNDEEP
c#sqlsql-serverprimary-key
Anoushka Seechurn
Anoushka Seechurn

Reputation: 2266

How to get PRIMARY KEY COLUMNS of a table (in case of COMPOSITE primary key)

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: 

 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.

Upvotes: 2

Views: 3602

Answers (4)

William Johnson
William Johnson

Reputation: 190

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 

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.

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

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

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();
      }

 }

Upvotes: 1

GarethD
GarethD

Reputation: 69829

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

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

Upvotes: 3

Ashutosh Arya
Ashutosh Arya

Reputation: 1168

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

Upvotes: 0

Related Questions