Query below lists tables and their primary key (PK) constraint names. By browsing 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 tab
    left outer join sys.indexes pk
        on tab.object_id = pk.object_id 
        and pk.is_primary_key = 1
   cross apply (select col.[name] + ', '
                    from sys.index_columns ic
                        inner join sys.columns 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 - schema name
• table_name - table name
• pk_name - primary key constraint name
• columns - list of PK columns separated with ','

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 are the names of PK constraints for each table and which tables don't have PKs at all (in Microsoft System Center Configuration Manager database).

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free