3 Ways to Find a Table’s Primary Key Constraint Name in SQL Server

Posted on by Ian

Primary keys are fundamental to relational database design, ensuring each row in a table can be uniquely identified. They help to maintain data integrity in our databases.

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 constraint itself.

Below are three examples of how we can do this.

Method 1: Using the Information Schema

One way to find the primary key constraint name is by querying the information schema. Here’s a SQL query that can help you retrieve this information:

SELECT
    constraint_name
FROM 
    information_schema.table_constraints
WHERE 
    table_name = 'Employees'
    AND table_schema = 'dbo'
    AND constraint_type = 'PRIMARY KEY';

Replace Employees with the name of the table you’re interested in and dbo with the schema name (if it’s different to dbo).

Method 2: Using sys.key_constraints

Another method involves using the sys.key_constraints view, which provides more detailed information about key constraints in the database:

SELECT
    name
FROM 
    sys.key_constraints
WHERE
    type = 'PK'
    AND parent_object_id = OBJECT_ID('dbo.Employees');

Replace dbo.Employees with your table’s name.

Method 3: Using sp_help Stored Procedure

If you prefer using stored procedures, you can use the sp_help system stored procedure:

EXEC sp_help 'dbo.Employees';

This will return multiple result sets with various information about the table, including its constraints. Look for the Constraint_type column in the results to find the PRIMARY KEY constraint and its name.

More on Primary Keys

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