Find Primary Key or ID column of Table in SQL Server 2000/2005

Introduction

This article discusses how to get or find Primary Key or ID column of a table in SQL Server 2000/2005.

sp_pkeys: This is a built-in stored procedure. This stored procedure requires only one parameter of table name. The result of that stored procedure is:

TABLE_ QUALIFIER	TABLE_OWNER	TABLE_NAME	COLUMN_NAME	KEY_SEQ	PK_NAME
NORTHWIND	DBO	ORDER	ORDERID	1	PK_ORDERS

TABLE_QUALIFIER is the database name which contains the table which we pass to the stored procedure (sp_pkeys).

You are building an application related to SQL data migration or any simple application. You need a primary key column Name /ID column name of a SQL table. Then this piece of code is very helpful or useful.

TABLE_OWNER is the owner who created that table, meaning table creator.

TABLE_NAME is the table name which we pass to the stored procedure (sp_pkeys).

COLUMN_NAME is our required result meaning this column Name Primary key column Name or ID column of that table. This is at the fourth index of reader if we are using SqlDataReader.

KEY_SEQ returns the column number, like first, second column.

PK_NAME returns how the primary key will be used in other tables as foreign key.

TABLE_ QUALIFIER	TABLE_OWNER	TABLE_NAME	COLUMN_NAME	KEY_SEQ	PK_NAME
NORTHWIND	DBO	ORDER	ORDERID	1	PK_ORDERS
NORTHWIND	DBO	ORDER	ORDERID1	2	PK_ORDERS

If your table has more than one primary key, then the result will be shown as below. This contains two primary key columns ORDERID, ORDERID1.

The below class PrimaryKeyFinder uses only one method GetprimaryKey. It requires only two parameters, one is Table Name and the other Connection String one constructor PrimaryKeyFinder().

The important thing is this class method is used here but you can call this method in any button click or use it in other functionality. And that method will return the primary key column Name. If you have more than one primary key column, meaning Composite keys, then use Array instead of string.

You can also check your table's primary key column by executing the stored procedure in query analyzer.

 Collapse | Copy Code

Like EXEC sp_pkeys ORDER 

Using the Code

I think when any person is working on SQL Server for specific information about databases or tables, he must check the built in features like built in stored procedure and views, etc.

 Collapse | Copy Code

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Collections;
public class PrimaryKeyFinder
{
public PrimaryKeyFinder ()
{
}
static void Main (string[] args)
{
String tableName=" ORDER";

String cnnString="Database connection here";

String PrimaryKeyColumnName=GetprimaryKey(tableName ,cnnString);
Console.Write("This is your tablePrimary Key Column"+ PrimaryKeyColumnName);
}

#region this Methods return ID column of table which we pass to it
public string GetprimaryKey(string tableName ,stringcnnString)
{
string names,
ID = "";
SqlDataReader mReader;
SqlConnection mSqlConnection = new SqlConnection();
SqlCommand mSqlCommand = new SqlCommand();
string cnString= cnString;
mSqlConnection = new SqlConnection(cnString);
mSqlConnection.Open();
// sp_pkeys is SQL Server default stored procedure
// you pass it only table Name, it will return
// primary key column
mSqlCommand = new SqlCommand("sp_pkeys",mSqlConnection);
mSqlCommand.CommandType = CommandType.StoredProcedure;mSqlCommand.Parameters.Add
   ("@table_name", SqlDbType.NVarChar).Value= tableName;
mReader = mSqlCommand.ExecuteReader();
while (mReader.Read())
{
//the primary key column resides at index 4 
ID = mReader[3].ToString();
}
return ID;
 }
}