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?
Reference: Pinal Dave (https://blog.sqlauthority.com)