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.
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]
- 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 ','
- 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 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.