List all the Primary Keys in SQL Server Database

The below script will give you list of Primary Keys in a database and columns which are part of the primary key definition.

If you don't know how to add a primary key on a table then you need to check out the "Adding Primary Key on a Table" first.


SELECT

TC.CONSTRAINT_NAME,
TC.TABLE_NAME ,
KCU.COLUMN_NAME,
ORDINAL_POSITION AS COLUMN_POSITION

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

WHERE 1=1
AND TC.TABLE_NAME = 'DEPT'
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

There could be several ways to get the same kind of information but from my point of view this is most simple way to do it.

If you do not want to limit your result set to a table then remove the TABLE_NAME condition.

Please let me know if you have a query which is much simple than this.....