How to determine the primary key for a table in SQL Server?
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.
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, 2022Comments
-
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 almost 15 yearsof course you would want to add a "where" clause. Where TABLE_NAME='mytable'
-
MattH almost 15 yearsThis doesn't get the column names involved in the primary key
-
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 almost 15 yearsSHEEESH! Learn to format code as code - highlight a section and click on the "code" button (010 101) in the toolbar - please!