List unique keys and indexes in Azure SQL Database

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:

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.