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

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">