List unique keys and indexes in Azure SQL Database

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

Table of Contents:

    The query below returns all primary keys, unique key constraints and unique indexes **on **tables and views in the Azure SQL database.

    Check out also the 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 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 ('') ) 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 the table or view
    • object_type - type of object on which the constraint/index was created
      • Table
      • View
    • constraint_type
      • 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 Azure SQL Database 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

    Sample results

    Primary, unique keys and 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.