List unique indexes in Azure SQL Database

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:

    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:

    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.