The query below lists tables and their primary key (PK) constraint names. By browsing the list, you can spot which tables have and which don't have primary keys.

See also: tables without primary keys.

Query

select schema_name(tab.schema_id) as [schema_name],
    tab.[name] as table_name,
    pk.[name] as pk_name,
    substring(column_names, 1, len(column_names)-1) as [columns]
from sys.tables as tab
    left outer 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),
    tab.[name]

Columns

• schema_name - name of the schema
• table_name - name of the table
• pk_name - primary key constraint name
• columns - list of PK columns separated by ','

Rows

• One row: represents one table in a database
• Scope of rows: all tables in a database
• Ordered by schema, table name

Sample results

You can see what the names of PK constraints are for each table and which tables don't have PKs at all.

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free