The query below lists all unique indexes in the database.

Check out also list of unique constraints.

Query

select i.[name] as index_name,
    substring(column_names, 1, len(column_names)-1) as [columns],
    case when i.[type] = 1 then 'Clustered unique index'
        when i.type = 2 then 'Unique index'
        end as index_type,
    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'
        end as constraint_type,
    c.[name] as constraint_name
from sys.objects as t
    left outer join sys.indexes as i
        on t.object_id = i.object_id
    left outer join sys.key_constraints as 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 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 = t.object_id
                    and ic.index_id = i.index_id
                        order by col.column_id
                        for xml path ('') ) as D (column_names)
where is_unique = 1
and t.is_ms_shipped <> 1
order by i.[name]

Columns

• index_name - name of the index
• columns - index columns separated with ","
• index_type - type of the index
  • Clustered unique index
  • Unique index - non-clustered unique index
• table_view - schema and name of the table or view
• object_type - type of object which constraint/index was created on
  • Table
  • View
• constraint_type -
  • Primary key - for primary keys
  • Unique constraint - for constraints created with CONSTRAINT UNIQUE statement
• constraint_name - primary/unique key constraint, null for unique indexes without constraints

Rows

• One row: represents one index
• Scope of rows: all unique indexes in the database
• Ordered by: index name

Sample results

Unique indexes in the AdventureWorksLT database: