Identify a Table’s Primary Key’s Index in SQL Server

Posted on by Ian

There are many ways to find the primary key column in SQL Server, but sometimes we might need to identify the name of the primary key’s index.

For example, we may want to identify the index’s name if we need to disable the primary key for some reason (we disable primary keys by disabling their index).

The query below can help if you need to find the name of a primary key’s index for a given table.

Example

Here’s a T-SQL query that will help you find the primary key index for a specific table:

SELECT
    name,
    type_desc,
    is_primary_key,
    is_disabled
FROM 
    sys.indexes
WHERE 
    object_id = OBJECT_ID('dbo.Employees')
AND is_primary_key = 1;

Replace dbo.Employees with the name of your table and schema.

Alternative Approach

If you want to find primary key indexes for all tables in a database, you can modify the query slightly:

SELECT
    name,
    type_desc,
    is_primary_key,
    is_disabled
FROM 
    sys.indexes
WHERE is_primary_key = 1;

Here, I simply omitted the part in the WHERE clause that narrowed the results down to just a specified table.

This provides a row for each primary key index in the database.

Understanding Primary Keys and Indexes

Let’s briefly review what primary keys and indexes are:

  • A primary key is a column or set of columns that uniquely identifies each row in a table.
  • An index is a data structure that improves the speed of data retrieval operations on a database table.
  • When you create a primary key constraint, SQL Server automatically creates a unique, clustered index for it (unless you specify otherwise).
  • If we want to disable a primary key, we disable its index.

So based on this knowledge, we know that when we get information about a primary key’s index, we’re not getting information about the primary key column itself or the constraint (unless we specifically want to). We’re getting information about the primary key’s index, which is a separate (but equally important) part of the primary key’s implementation.

More About Primary Keys

Here are some more articles that I’ve written about primary keys that you may be interested in: