List all primary keys (PKs) and their columns in SQL Server database

Query below lists all primary keys constraints (PK) in the database with their columns (one row per column).

See also: list of all primary keys (one row per PK).


select schema_name(tab.schema_id) as [schema_name], 
    pk.[name] as pk_name,
    ic.index_column_id as column_id,
    col.[name] as column_name, 
    tab.[name] as table_name
from sys.tables tab
    inner join sys.indexes pk
        on tab.object_id = pk.object_id 
        and pk.is_primary_key = 1
    inner join sys.index_columns ic
        on ic.object_id = pk.object_id
        and ic.index_id = pk.index_id
    inner join sys.columns col
        on pk.object_id = col.object_id
        and col.column_id = ic.column_id
order by schema_name(tab.schema_id),


  • schema_name - PK schema name
  • pk_name - PK constraint name
  • column_id - id of column in index (1, 2, ...). 2 or higher means key is composite (contains more than one column)
  • column_name - primary key column name
  • table_name - PK table name


  • One row represents one primary key column
  • Scope of rows: columns of all PK constraints in a database
  • Ordered by schema, PK constraint name, column id

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.