List unique indexes in Azure SQL Database

The query below lists all unique indexes in the database.

Check out also list of unique constraints.


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]


  • 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


  • 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:

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.