Find all Primary Key Columns from a SQL Server table

There can only be one primary key in a table, but it is possible to have a composite primary key - a primary key made from two or more columns.

Here is a query that will list all Primary Key columns from a SQL Server table (enter the schema and table name in the WHERE statement - in this case we want to find Primary Key columns from Person.BusinessEntityAddress table):

SELECT DB_NAME() AS Database_Name
,sc.name AS 'Schema_Name'
,o.Name AS 'Table_Name'
,i.Name AS 'Index_Name'
,c.Name AS 'Column_Name'
,ic.key_ordinal
,i.type_desc AS 'Index_Type'
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.is_primary_key = 1
AND sc.name = 'Schema_Name'
AND o.name = 'Table_Name'
ORDER BY o.Name, i.Name, ic.key_ordinal

To list all Primary Key columns from all tables in a database:

SELECT DB_NAME() AS Database_Name
,sc.name AS 'Schema_Name'
,o.Name AS 'Table_Name'
,i.Name AS 'Index_Name'
,c.Name AS 'Column_Name'
,ic.key_ordinal
,i.type_desc AS 'Index_Type'
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.is_primary_key = 1
ORDER BY o.Name, i.Name, ic.key_ordinal