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