Query below returns all primary keys and unique key constraints and unique indexes on tables and views in SQL Server database.
Check out also list of unique keys.
select schema_name(t.schema_id) + '.' + t.[name] as table_view, case when t.[type] = 'U' then 'Table' when t.[type] = 'V' then 'View' end as [object_type], case when c.[type] = 'PK' then 'Primary key' when c.[type] = 'UQ' then 'Unique constraint' when i.[type] = 1 then 'Unique clustered index' when i.type = 2 then 'Unique index' end as constraint_type, c.[name] as constraint_name, substring(column_names, 1, len(column_names)-1) as [columns], i.[name] as index_name, case when i.[type] = 1 then 'Clustered index' when i.type = 2 then 'Index' end as index_type from sys.objects t left outer join sys.indexes i on t.object_id = i.object_id left outer join sys.key_constraints c on i.object_id = c.parent_object_id and i.index_id = c.unique_index_id 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 = t.object_id and ic.index_id = i.index_id order by col.column_id for xml path ('') ) D (column_names) where is_unique = 1 and t.is_ms_shipped <> 1 order by schema_name(t.schema_id) + '.' + t.[name]
- table_view - schema and name of table or view
- object_type - type of object which constraint/index was created on
- Primary key - for primary keys
- Unique constraint - for constraints created with CONSTRAINT UNIQUE statement
- Unique clustered index - unique clustered index without primary/unique constraint
- Unique index - unique non-clustered index without primary/unique constraint
- constraint_name - primary/unique key constraint, null for unique indexes without constraints
- columns - index columns separated with ","
- index_name - name of the index
- index_type - type of the index
- Clustered index - clustered index
- Index - non-clustered index
- One row represents one constraint/index in the database. Primary/unique key constraints are implemented in SQL Server as indexes and such pair is represented as one row
- Scope of rows: all PKs, UKs and unique indexes
- Ordered by schema and table/view name
Primary, unique keys and unique indexes in AdventureWorks database: