Tuesday, May 13, 2008

Finding the primary key

Here is a script to list the primary key or keys on @tablename in SQL Server:
SELECT sc.name
FROM sys.indexes si
INNER JOIN sys.index_columns sic ON si.index_id = sic.index_id
AND si.object_id = sic.object_id
INNER JOIN sys.columns sc ON sc.column_id = sic.column_id
AND si.object_id = sc.object_id
WHERE si.is_primary_key <> 0
AND si.object_id = OBJECT_ID(@tablename)

I am posting this, since I tried to google it, and most people's answers were way off in the weeds.