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

14,371

Solution 1

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

Solution 2

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

Solution 3

Try This:

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

Solution 4

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

Solution 5

select *
from information_schema.Table_Constraints
where Table_Name = @tableName

See this MSDN Listing for Table Constraints.

Share:
14,371
tekiegreg
Author by

tekiegreg

SharePoint Developer and Administrator since 2007. Currently the go to guy for SharePoint at Colorado Parks and Wildlife. Wild SharePoint if you will :-) Stuff I like doing: .NET, JavaScript, Jquery, Laying out farms, upgrading farms, maintaining farms, etc. Also Worked for in my Past for SharePoint: E-470, Starbucks, Other State of Colorado Agencies, Blue Cross/Blue Shield and more. When I'm not doing SharePoint I'm also heavily into Toastmasters (Currently Area Director, nominated Eastern Division Director in District 26), and more, ask if you're really curious.

Updated on June 04, 2022

Comments

  • tekiegreg
    tekiegreg about 2 years

    What I'd like to be able to do in SQL Server 2005 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?

  • ChickenMilkBomb
    ChickenMilkBomb almost 15 years
    of course you would want to add a "where" clause. Where TABLE_NAME='mytable'
  • MattH
    MattH almost 15 years
    This doesn't get the column names involved in the primary key
  • MattH
    MattH almost 15 years
    @Nick you could edit the post rather than add a comment, also could add some code formatting as your queries aren't easy to read +1 for ANSI approach though
  • marc_s
    marc_s almost 15 years
    SHEEESH! Learn to format code as code - highlight a section and click on the "code" button (010 101) in the toolbar - please!