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.
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]
- schema_name - schema name
- table_name - table name
- pk_name - primary key constraint name
- columns - list of PK columns separated with ','
- One row represents one table in a database
- Scope of rows: all tables in a database
- Ordered by schema, table name
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.