List all primary keys (PKs) in a database

The query below lists all primary keys constraints (PK) in the database.

See also: tables with their primary keys.


select schema_name(tab.schema_id) as [schema_name],
    pk.[name] as pk_name,
    substring(column_names, 1, len(column_names)-1) as [columns],
    tab.[name] as table_name
from sys.tables as tab
    inner join sys.indexes as pk
        on tab.object_id = pk.object_id
        and pk.is_primary_key = 1
    cross apply (select col.[name] + ', '
                from sys.index_columns as ic
                    inner join sys.columns as col
                        on ic.object_id = col.object_id
                        and ic.column_id = col.column_id
                where ic.object_id = tab.object_id
                        and ic.index_id = pk.index_id
                order by col.column_id
                    for xml path ('') ) D (column_names)
order by schema_name(tab.schema_id),


  • schema_name - PK schema name
  • pk_name - PK constraint name
  • columns - list of PK columns separated with ','
  • table_name - PK table name


  • One row: represents one primary key (table) in a database
  • Scope of rows: all PK constraints in a database
  • Ordered by: schema, PK constraint name

Sample results

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.