sp_pkeys and Primary Key Metadata in SQL Server

calendar Jun 16, 2026 / · 6 min read · sql server sp_pkeys sys.key_constraints sys.index_columns sys.columns sys.types sys.tables sys.objects sys.schemas information_schema primary keys catalog views metadata database structure scripts  ·
Share on: linkedin copy

Which columns make up a table's primary key, and in what order are they declared? For a single table, the system stored procedure sp_pkeys answers in one line; for a database-wide inventory, the catalog views behind it give you full control. This post covers both, from the quick lookup to the complete metadata query.

Purpose and Overview

sp_pkeys returns primary key information for one table in the current database. It is an ODBC-catalog procedure — the T-SQL equivalent of the SQLPrimaryKeys ODBC function — and its result set is deliberately compact: TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, KEY_SEQ, and PK_NAME. The KEY_SEQ column is the one that matters most: it gives the sequence number of each column within a multicolumn primary key, so a composite key returns one row per column in declared order.

The procedure is ideal for a fast, scriptable check of a known table, and because its shape is standardized across drivers, tools and ORMs rely on it heavily. What it cannot do is report across an entire database, return unique constraints, or expose the underlying index — for those you go to the catalog views it sits on top of.

Those views are sys.key_constraints (one row per primary key or unique constraint), sys.index_columns (the columns of the enforcing unique index, in key order), and sys.columns. They are part of the same proprietary sys catalog layer used throughout SQL Server metadata work. If you need a portable, ANSI-standard alternative, INFORMATION_SCHEMA.KEY_COLUMN_USAGE provides one, as discussed in the data-dictionary queries post. For related tasks, see primary key discovery and finding tables without primary keys.

Code Breakdown

The quick lookup for a single table:

1EXEC sp_pkeys
2    @table_name  = N'SalesOrderHeader',
3    @table_owner = N'Sales';

The full database-wide inventory, returning every primary key column in declared order with its data type:

 1SELECT
 2    s.name              AS schema_name,
 3    t.name              AS table_name,
 4    kc.name             AS pk_name,
 5    ic.key_ordinal,
 6    c.name              AS column_name,
 7    ty.name             AS data_type,
 8    ic.is_descending_key
 9FROM sys.key_constraints AS kc
10INNER JOIN sys.tables AS t
11    ON kc.parent_object_id = t.object_id
12INNER JOIN sys.schemas AS s
13    ON t.schema_id = s.schema_id
14INNER JOIN sys.index_columns AS ic
15    ON  kc.parent_object_id = ic.object_id
16    AND kc.unique_index_id  = ic.index_id
17INNER JOIN sys.columns AS c
18    ON  ic.object_id = c.object_id
19    AND ic.column_id = c.column_id
20INNER JOIN sys.types AS ty
21    ON c.user_type_id = ty.user_type_id
22WHERE kc.type = 'PK'
23ORDER BY s.name, t.name, ic.key_ordinal;

Anchoring on sys.key_constraints

sys.key_constraints holds a row for every primary key and unique constraint, distinguished by its type column (PK versus UQ). Filtering WHERE kc.type = 'PK' isolates primary keys. Because this view inherits from sys.objects, it also carries the constraint name and parent_object_id, which links the constraint back to its table.

Following unique_index_id to the columns

A primary key is enforced by a unique index, and sys.key_constraints.unique_index_id is the ID of that index. Joining it to sys.index_columns on both object_id and index_id returns the columns of the enforcing index. The key_ordinal column then orders them exactly as the key was declared — the catalog-view equivalent of sp_pkeys's KEY_SEQ.

Resolving names and data types

sys.index_columns stores only numeric column_id values, so the query joins to sys.columns to get column names and to sys.types to get the data-type name. Including the data type makes the output useful for documentation and for spotting wide or inappropriate key columns, such as a uniqueidentifier or a long varchar carrying the clustered key.

The ANSI alternative

INFORMATION_SCHEMA.KEY_COLUMN_USAGE returns one row per key column with an ORDINAL_POSITION that mirrors key_ordinal. Joined to INFORMATION_SCHEMA.TABLE_CONSTRAINTS filtered on CONSTRAINT_TYPE = 'PRIMARY KEY', it produces a portable version of the same report at the cost of the type and index detail the sys views expose:

 1SELECT
 2    kcu.TABLE_SCHEMA,
 3    kcu.TABLE_NAME,
 4    tc.CONSTRAINT_NAME AS pk_name,
 5    kcu.ORDINAL_POSITION,
 6    kcu.COLUMN_NAME
 7FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
 8INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
 9    ON  tc.CONSTRAINT_NAME   = kcu.CONSTRAINT_NAME
10    AND tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
11WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
12ORDER BY kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.ORDINAL_POSITION;

The join is on constraint name and schema, because constraint names are unique only within a schema. This form runs unchanged on any ISO-compliant engine, which makes it the right choice for tooling that must target more than SQL Server. The trade-off is depth: it cannot tell you the enforcing index, whether a key column is descending, or the underlying data type, all of which the catalog-view query above returns. Choose the sys views when you need that detail and the INFORMATION_SCHEMA form when portability wins.

Why KEY_SEQ and key_ordinal matter

Both sp_pkeys's KEY_SEQ and sys.index_columns's key_ordinal report the position of a column within the key, and that order is not cosmetic. A composite primary key is physically an index on those columns in that sequence, so the leading column drives seekability: a query that filters on the first key column can seek efficiently, while one that filters only on a later column cannot. Reading the declared order tells you which access patterns the key supports — a routine check when diagnosing why a query on a multi-column key is scanning rather than seeking.

Key Benefits and Use Cases

  • One-line single-table lookup — sp_pkeys is the fastest way to confirm a table's key and column order interactively.
  • Database-wide inventory — the catalog-view query lists every primary key in the database, with column order and data type.
  • Composite-key clarity — key_ordinal / KEY_SEQ reveal the exact declared order, which drives index seek efficiency.
  • Migration and ORM checks — verify that every table has a key before enabling features (replication, change tracking) that require one.
  • Portable option — the INFORMATION_SCHEMA form runs unchanged across SQL platforms that implement the ISO views.

Performance Considerations

  • sp_pkeys is single-table only: it takes a table name and cannot scan a whole database. For inventory work, use the catalog-view query instead of looping sp_pkeys over every table.
  • Permission scoping applies: both sp_pkeys and the catalog views respect metadata visibility, so a low-privilege user may see fewer keys than exist.
  • @table_owner matters: without it, sp_pkeys falls back to default visibility rules and may resolve to a different schema's table of the same name — always pass the owner in multi-schema databases.
  • Catalog reads are cheap: these are metadata-only queries, but on huge schemas filter by OBJECT_ID('schema.table') to avoid returning every key at once.

Practical Tips

  • To find tables that lack a primary key entirely, query sys.tables and use OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0 — the inverse of this report.
  • Swap WHERE kc.type = 'PK' to 'UQ' to inventory unique constraints with the identical join.
  • Add ic.is_descending_key (already in the query) to detect descending key columns, which affect index ordering and range-scan direction.
  • Capture the inventory to a table during a release and diff it afterward to catch unintended key changes in deployment scripts.
  • When documenting for developers, concatenate the ordered column names with STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) to show each key as a single readable column list.

Conclusion

sp_pkeys is the right tool when you know the table and want its key fast; the sys.key_constraints → sys.index_columns → sys.columns join is the right tool when you need every primary key in the database with column order and types. Both surface the same underlying metadata, and INFORMATION_SCHEMA.KEY_COLUMN_USAGE offers a portable third option. Together they cover every primary-key question a DBA routinely has to answer.

References

Posts in this series