List tables with their primary keys (PKs) in SQL Server database

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.

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).