How To Get The Primary Key Of A Table In SQL Server

As a SQL developer, as part of my daily tasks, I used to work on multiple issues where I used to find the primary key of a table in an SQL server. In this article, I will walk you through multiple approaches.

How to find the primary key of a table in SQL Server

Let us dive deeply into all the approaches individually.

Approach-1: Using INFORMATION_SCHEMA.TABLE_CONSTRAINTS

We can execute the below SQL query to retrieve the primary key column details of the Order table.

SELECT 
    c.TABLE_NAME AS TableName,
    COLUMN_NAME AS PrimaryKeyColumn
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
    ON T.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND T.CONSTRAINT_NAME = c.CONSTRAINT_NAME
    AND c.TABLE_NAME = 'Order'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION;

After executing the above script, I got the expected output, as shown below.

Check out How To Check If Column Exists In SQL Server Database

Approach-2: Using sp_pkeys stored procedure

To quickly find the primary key details of a table in SQL server, you can also execute the sp_pkeys stored procedure.

Syntax

EXEC sp_pkeys 'YourTableName';

Example

We can execute the below query to get the primary key column details of the Order table.

EXEC sp_pkeys 'Order';

After executing the above query, I got the expected output, as shown in the screenshot below.

Check out How To Add A Column To A Table In SQL Server

Approach-3: Using SQL Server Management Studio (SSMS)

Follow the below steps.

1. Connect to the SQL server and expand the Database folder.

2. Then, expand the specific database node.

3. Now, expand the table node under the specific database.

4. Expand the specific table node and then expand the Columns folder. You will see the key symbol before the primary key column’s name, as shown in the screenshot below.

Check out How To Count Number Of Columns In A Table In SQL Server

Approach-4: Using the System Catalog Views

We can also execute the below query to retrieve the primary key details of the Order table.

SELECT 
    TableName = t.name,
    ColumnName = s.name
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns id ON i.object_id = id.object_id AND i.index_id = id.index_id
INNER JOIN 
    sys.columns s ON id.object_id = s.object_id AND id.column_id = s.column_id
INNER JOIN 
    sys.tables t ON i.object_id = t.object_id
WHERE 
    i.is_primary_key = 1
    AND t.name = 'Order';

After executing the above query, I got the expected output below.

Conclusion

Knowing the primary key details of a table in an SQL server is crucial for a SQL developer to manage the database and troubleshoot any critical issues. You can utilize any of the approaches mentioned in this article to achieve this task.

You may also like following the articles below.

• How to Alter Table Column to Allow NULL in SQL Server
• How To Enable CDC On A Table In SQL Server
• Cannot Define Primary Key Constraint On Nullable Column In Table