Show Primary Key of a Table in SQL Server With sp_pkeys

To show primary key columns for one table, run sp_pkeys with the table name. The procedure returns one row for every column in the key. A table with no key returns nothing.

Why the Key Columns Matter

A primary key says how a table tells its rows apart. Know it before you write a join, add a foreign key or choose the match columns for a MERGE. You need it again when you compare two environments, because a key that differs between them breaks a deployment.

Management Studio shows the key as a small icon, one table at a time. A query answers faster, and you can paste the result into a ticket. The procedure sp_pkeys is the shortest query for one table.

Build a Small Demo

The demo database is named PrimaryKeyDemo. It holds four tables. Orders has a one column key and a unique constraint. OrderLines has a two column key and a foreign key to Orders. Staging has no key, and shop.Orders lives in a second schema. Run the script on a test server.

IF DB_ID(N'PrimaryKeyDemo') IS NULL CREATE DATABASE PrimaryKeyDemo;
GO
USE PrimaryKeyDemo;
GO
IF SCHEMA_ID(N'shop') IS NULL EXEC (N'CREATE SCHEMA shop');
GO
DROP TABLE IF EXISTS dbo.OrderLines, dbo.Orders, dbo.Staging, shop.Orders;
CREATE TABLE dbo.Orders (
    OrderID   int          NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    OrderDate date         NOT NULL,
    Email     nvarchar(80) NOT NULL CONSTRAINT UQ_Orders_Email UNIQUE
);
CREATE TABLE dbo.OrderLines (
    OrderID    int          NOT NULL,
    LineNumber int          NOT NULL,
    Sku        nvarchar(20) NOT NULL,
    CONSTRAINT PK_OrderLines PRIMARY KEY (OrderID, LineNumber),
    CONSTRAINT FK_OrderLines_Orders FOREIGN KEY (OrderID) REFERENCES dbo.Orders (OrderID)
);
CREATE TABLE dbo.Staging (Id int NOT NULL, Note nvarchar(50) NULL);
CREATE TABLE shop.Orders (ShopOrderID bigint NOT NULL CONSTRAINT PK_shop_Orders PRIMARY KEY);

Show Primary Key Columns With sp_pkeys

sp_pkeys is a system stored procedure. It reads the catalog of the current database, so run it in the database that holds the table. The only required argument is the table name.

EXEC sp_pkeys @table_name = N'OrderLines';

Every row is one key column. A composite key returns one row per column, and KEY_SEQ gives the column’s place in the key. The two rows share one PK_NAME, which is the constraint’s name. The qualifier is the database, and the owner is the schema.

Only primary keys appear. The unique constraint on Orders.Email is missing from the result on purpose. A unique constraint is a different object, so look for it in the catalog views instead.

Name the Schema and Expect Empty Results

Two schemas can hold a table with the same name. Pass @table_owner to choose one. With no owner, the call for Orders returned the dbo table only. The procedure looks in the caller’s schema first and then in dbo. Here dbo is the default schema of the demo login.

EXEC sp_pkeys @table_name = N'Orders';

EXEC sp_pkeys @table_name = N'Orders', @table_owner = N'shop';

EXEC sp_pkeys @table_name = N'Staging';

EXEC sp_pkeys @table_name = N'Missing';

Staging and Missing return an empty result with the column headings and no error. An empty result means one of two things: the table has no key, or the name is wrong. A view returns the same empty result, because a view has no key. Check the spelling before you decide a table has no key.

The qualifier argument is stricter. It must name the current database, or the call fails. The line number in the message depends on your build.

EXEC sp_pkeys @table_name = N'Orders', @table_owner = N'dbo', @table_qualifier = N'master';

Msg 15250, Level 16, State 1, Procedure sp_pkeys, Line 17
The database name component of the object qualifier must be the name of the current database.

Skip the qualifier. It adds nothing, because the procedure can’t look into another database.

Show Primary Key Columns for Every Table

sp_pkeys answers one table at a time. To show primary key columns for a whole database, read the catalog views. This query joins each table to its key and puts the key columns into one list, in key order. STRING_AGG needs SQL Server 2017 or later.

SELECT s.name AS SchemaName, t.name AS TableName, kc.name AS PrimaryKeyName,
       STRING_AGG(c.name, N', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.key_constraints AS kc ON kc.parent_object_id = t.object_id AND kc.type = 'PK'
JOIN sys.index_columns AS ic ON ic.object_id = kc.parent_object_id AND ic.index_id = kc.unique_index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
GROUP BY s.name, t.name, kc.name
ORDER BY s.name, t.name;

The result has one row per key, so a composite key stays on one line. Join sys.indexes as well if you need to know whether the key is clustered. sp_pkeys can’t tell you that.

Find Tables Without a Primary Key

A table with business data needs a key. Without one, the same row can be stored twice, and nothing tells the twins apart. This query lists every table that has no key.

SELECT s.name AS SchemaName, t.name AS TableName
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE OBJECTPROPERTY(t.object_id, 'TableHasPrimaryKey') = 0
ORDER BY s.name, t.name;

A staging table with no key can be fine. The list is a set of questions, not a set of errors. For each table on it, I ask what makes one row different from the next.

Name Your Keys

A key you don’t name gets a name from SQL Server. The next script creates a throwaway table with an unnamed key and reads the name back.

CREATE TABLE dbo.Unnamed (Id int NOT NULL PRIMARY KEY);
SELECT name FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID(N'dbo.Unnamed');
DROP TABLE dbo.Unnamed;

The name combines PK, the start of the table name and a hex code. An example is PK__Unnamed__ followed by sixteen hex digits. Part of that code changes each time the table is created. A script that compares key names between two databases then reports a difference that isn’t one. Name every key in your own scripts, as the demo does.

The Foreign Key Counterpart

The procedure sp_fkeys works the other way round. Give it the table that holds the primary key, and it returns every column that refers to it.

EXEC sp_fkeys @pktable_name = N'Orders', @pktable_owner = N'dbo';

The procedure returns more columns than shown here, such as the update and delete rules. They are codes: 1 means NO ACTION and 0 means CASCADE. Read them before you plan a delete.

You could argue that sp_pkeys is a leftover. It was built for ODBC drivers. The catalog views report more, such as the clustered flag and the index options. That’s fair. For a whole database, I use the views. For one table and one quick question, a one line call is easier to type and to remember.

What to Remember

Show primary key columns with sp_pkeys when you have one table and one question. Name the schema when a table name repeats. Treat an empty result as a prompt to check the spelling, not as proof that the key is missing.

Use the catalog query when you need every table at once. Use the OBJECTPROPERTY query to find tables without a key, and name every key you create. When you finish testing, drop the demo database.

USE master;
GO
IF DB_ID(N'PrimaryKeyDemo') IS NOT NULL
BEGIN
    ALTER DATABASE PrimaryKeyDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PrimaryKeyDemo;
END;

A primary key is not a column, it is a promise the table keeps.

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