How do you list the primary key of a SQL Server table?

sql

database-management

sql-server

primary-key

byNikita Barsukov·Jan 14, 2025

The primary key column(s) of a SQL Server table can be fetched through this efficient T-SQL query:

SELECT kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
  ON tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
  AND tc.TABLE_NAME = kcu.TABLE_NAME
  AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
  AND tc.TABLE_NAME = 'YourTableName';

All you need to do is replace 'YourTableName' to reveal the primary key column(s) for your specific table.

Significance of a primary key

Recognizing and extracting the primary key from a table is a cornerstone in database management. The primary key is the unique identifier in every record, ensuring data integrity and improving query optimization. Think of it as the "one ring to rule them all" in your table, without it, there's chaos!

Peek into system catalogs for primary key detection

To gain a deeper knowledge of your SQL Server database’s structure, system catalog views are your friends. Using sys.indexes in coordination with other system catalog views can give insight into your database's indexes, and make you feel like Sherlock Holmes solving the "case of the primary key."

SELECT col.name
FROM sys.indexes idx  -- Elementary, my dear Watson!
JOIN sys.index_columns idxCol ON idx.object_id = idxCol.object_id AND idx.index_id = idxCol.index_id
JOIN sys.columns col ON idx.object_id = col.object_id AND idxCol.column_id = col.column_id
WHERE idx.object_id = OBJECT_ID('YourTableName') AND idx.is_primary_key = 1;  -- Here's our key suspect!

Choice between the two paths

Both methods above work excellent, but here's your deciding factor:

INFORMATION_SCHEMA queries are the action heroes of the SQL-world, they work across databases and platforms and are easier to read.
System catalog views (sys.indexes and related views) are specific to SQL Server. They provide extra details, but with great power comes greater complexity.

Dealing with table schema

Table names can be duplicates, but in different schemas. To ensure you are retrieving data about the right table, don't forget to include the TABLE_SCHEMA in your query:

SELECT kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
  ON tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
  AND tc.TABLE_NAME = kcu.TABLE_NAME
  AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
  AND tc.TABLE_NAME = 'YourTableName'
  AND tc.TABLE_SCHEMA = 'YourSchemaName';  -- Dress code: Schema Name

Catching composite keys

In some cases, a primary key is made up of multiple columns, like a band of superheroes! This configuration is known as a composite primary key. Here's how you can identify the members of this team:

SELECT kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
  ON tc.TABLE_NAME = kcu.TABLE_NAME
  AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
  AND tc.TABLE_NAME = 'YourTableName'
ORDER BY kcu.ORDINAL_POSITION;

Special cases and their treatments

When exploring the SQL database, be ready to encounter a few anomalies. Here are some which may come up:

Partitioned Tables: Ensure the primary key synchronizes with the partition function.
Filtered Indexes: Primary keys can also partake in filtered indexes – this changes how data is managed and queried.
Disabled Indexes: Primary keys sometimes take a break for maintenance operations; remember, just because they're not working doesn't mean they're not there.
System-versioned Temporal Tables: They have historical records where primary key considerations could differ.