Wednesday, February 7, 2007

SQL Server - Find primary keys in a table

To find primary key in a given table :

Option 1: sp_pkeys 'yourtablename'

Option 2: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = 'yourtablename' and constraint_type = 'primary key'

Option 3: sp_help 'yourtablename'

To find all primary keys in database:

Use yourdatabasename
select c.COLUMN_NAME ,c.CONSTRAINT_NAME,C.table_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

These would work for both SQL Server 2K and SQL Server 2K.

No comments: