How to determine a primary key for a table in SQL Server?

Question

What I'd like to be able to do via SQL somehow is with a table name as input determine all the fields that make up the primary key. sp_columns doesn't seem to have this field. Any ideas as to where to look?

EDIT: This is SQL Server 2005

Statement on Answer: While others may have been right his was the cleanest and easiest to implement :-)

Answer 1

-- ANSI SQL compatible and works from SQL70 onwards:

select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
  join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
    on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
   and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
   and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
   and kcu.TABLE_NAME = tc.TABLE_NAME
 where tc.CONSTRAINT_TYPE in ( 'PRIMARY KEY', 'UNIQUE' )
 order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;

-- SQL Server 2005 specific:

select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
     , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
     , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
  from sys.key_constraints as k
  join sys.tables as t
    on t.object_id = k.parent_object_id
  join sys.schemas as s
    on s.schema_id = t.schema_id
  join sys.index_columns as ic
    on ic.object_id = t.object_id
   and ic.index_id = k.unique_index_id
  join sys.columns as c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
 order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, ORDINAL_POSITION;

Answer 2

I use this in a code generator I wrote to get the primary key:

SELECT i.name AS IndexName, 
OBJECT_NAME(ic.OBJECT_ID) AS TableName, 
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName, 
c.is_identity, c.user_type_id, CAST(c.max_length AS int) AS max_length, CAST(c.precision AS int) AS precision, CAST(c.scale AS int) AS scale 
FROM sys.indexes AS i 
INNER JOIN sys.index_columns AS ic 
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
ON i.OBJECT_ID = ic.OBJECT_ID 
AND i.index_id = ic.index_id 
WHERE i.is_primary_key = 1 AND ic.OBJECT_ID = OBJECT_ID('dbo.YourTableNameHere')
ORDER BY OBJECT_NAME(ic.OBJECT_ID), ic.key_ordinal

Answer 3

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE table_name = 'your_table_name' AND constraint_name LIKE 'PK%'

Answer 4

select *
from information_schema.Table_Constraints
where Table_Name = @tableName

See this MSDN Listing for Table Constraints.

Answer 5

I normally find that...

sp_help <table>

gives me all I need to know about a table (including index information).

Answer 6

Actually the primary key is something else than the indexes on the table. Is also something else than the clustered index. Is a constraint, so the proper place to look for it is sys.key_constraints:

select ic.key_ordinal, cl.name, ic.is_descending_key
from sys.key_constraints c
join sys.indexes i on c.parent_object_id = i.object_id
    and c.unique_index_id = i.index_id
join sys.index_columns ic on ic.object_id = i.object_id
    and ic.index_id = i.index_id 
join sys.columns cl on cl.object_id = i.object_id
    and ic.column_id = cl.column_id 
where c.type = 'PK'
    and 0 = ic.is_included_column
    and i.object_id = object_id('<tablename>')
order by ic.key_ordinal

Answer 7

In SQL2005 this brings back a row that names the primary key and then gives a list of the column under "index_keys"

sp_help myTable