SQL: How to find primary key column names in SQL Server
Another question that I answered on a forum recently was about how to find the primary key column (or columns) for a table.
Here’s an example of the code required:
SELECT ic.index_column_id AS ColumnID,
c.[name] AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
AND ic.column_id = c.column_id
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE i.is_primary_key <> 0
AND t.[name] = N'TestTable' -- table name
AND s.[name] = N'dbo' -- schema name
ORDER BY ColumnID;
I hope that helps someone.
2019-10-17
