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.

Query

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]

Columns

  • 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

Rows

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

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