Click here to Skip to main content
Click here to Skip to main content
Go to top

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

, 21 Sep 2007
Rate this:
Please Sign up or sign in to vote.
Get or 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.

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.

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

History

  • 21st September, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

idreeskhan
Web Developer
Pakistan Pakistan
I'm from Pakistan and have two years of experience in C# and Asp.net in visual stdio 2005 ,2008, 2010 and SQL Server 2000/2005/2008

Comments and Discussions

 
QuestionThis piece of code might be very useful Pinmembermanjunath bhat3-Oct-11 23:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 22 Sep 2007
Article Copyright 2007 by idreeskhan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid