How do you list all the primary keys of a table in SQL Server?

Question

Simple question, how do you list all the primary keys of a table with T-SQL? I know how to get indexes on a table, but can't remember PK's.

Answer 1

The system stored procedure sp_help will give you this information. Execute the following statement:

execute sp_help tablename

Answer 2

SELECT Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY '
    AND Col.Table_Name = '<your table name>'

Answer 3

Is using MS SQL Server you can do the following:

--List all tables primary keys select * from information_schema.table_constraints where constraint_type = 'Primary Key'

You can also filter on the table_name column if you want a specific table.

Answer 4

Give this a try:

select * 
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
where CONSTRAINT_TYPE = 'Primary Key' and Table_Name = 'whatever'

Answer 5

SELECT t.name as 'table',i.name as 'index',it.xtype,

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=1 and k.id=t.id)as 'column1',

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=2 and k.id=t.id)as 'column2',

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=3 and k.id=t.id)as 'column3',

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=4 and k.id=t.id)as 'column4',

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=5 and k.id=t.id)as 'column5',

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=6 and k.id=t.id)as 'column6',

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=7 and k.id=t.id)as 'column7',

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=8 and k.id=t.id)as 'column8',

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=9 and k.id=t.id)as 'column9',

(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=10 and k.id=t.id)as 'column10'

from sysobjects t inner join sysindexes i on i.id=t.id inner join sysobjects it on it.parent_obj=t.id and it.name = i.name

WHERE it.xtype='PK' order by t.name, i.name

Answer 6

I like the INFORMATION_SCHEMA technique, but another I've used is: exec sp_pkeys 'table'

Answer 7

Thanks Guy.

With a slight variation I used it to find all the primary keys for all the tables.

SELECT A.Name,Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ,
    (select NAME from dbo.sysobjects where xtype='u') AS A
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY '
    AND Col.Table_Name = A.Name