How to get the column name of a PRIMARY KEY table

Question

I need to get the name PRIMARY KEY COLUMN NAME. I have the name of my table in a variable called _lstview_item

So far, I have been trying to get a column name like this

  string sql = "SELECT ColumnName = 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 = " +_lstview_item+ ""; SqlConnection conn2 = new SqlConnection(cc.connectionString(cmb_dblist.Text)); SqlCommand cmd_server2 = new SqlCommand(sql); cmd_server2.CommandType = CommandType.Text; cmd_server2.Connection = conn2; conn2.Open(); string ColumnName = (string)cmd_server2.ExecuteScalar(); conn2.Close(); 

Without success. Help?

Answer 1

This should be your request. You are missing single quotes in the name of your table. Tested and works great.

 string sql = "SELECT ColumnName = 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 = '" + _lstview_item + "'"; 

Answer 2

try the following:

 SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = 'TableName'