SQL SERVER – Show Primary Key for a Single Table – sp_pkeys

When I work with Comprehensive Database Performance Health Check, I get all sort of questions and I love answering them. Recently, one of my clients showed me a very cool trick to check Primary Key for a single table with the help of the system stored procedure sp_pkeys. Let us discuss that in this blog post.

DMV and Primary Keys

Earlier anytime if anyone asks me to display the Primary Keys, my go-to method was to use DMV to display necessary information. Here is the script which I always used to display Primary Key.

SELECT, OBJECT_SCHEMA_NAME(parent_object_id) SchemaName,
OBJECT_NAME(parent_object_id) AS 'Table',
name PrimaryKeyName
FROM sys.key_constraints
WHERE type = 'PK';

However, my client taught me a very interesting trick with the help of sp_pkeys, if I want to get detail of a single table. Let us see that here.

sp_pkeys and Primary Keys

If you use DMV, you can get all the necessary information and I am going to continue using it for all my consulting engagements. However, if you want to see the details for a single table you can also use sp_pkeys system stored procedure as well.

EXEC sp_pkeys Orders

You will see similar resultset for your query.

There are many different ways to use sp_pkeys and you can also specify table_owner and schema name (table qualifier) as well.

Here is an example of the same.

EXEC sp_pkeys
@table_name = Orders,
@table_owner = dbo,
@table_qualifier = SQLAuthority;

Please note that in either case, you will get the same result. I am personally not sure if I will use this method to see the information about the primary key. In any case, it is interesting to know the usage of these keywords.

Here are a few additional blog posts on the same topic which may interest you:

• Can We Have a NULL Value in Primary Key? – Interview Question of the Week #075
• Primary Key and Null in SQL Server – Interview Question of the Week #071

I want to ask all of you – were you aware of the system stored procedure which displays the information about the primary key?

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.