List table indexes in Azure SQL Database

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

Table of Contents:


    The query below lists table (and view) indexes.

    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],
        i.index_id,
        case when i.is_primary_key = 1 then 'Primary key'
            when i.is_unique = 1 then 'Unique'
            else 'Not unique' end as [type],
        i.[name] as index_name,
        substring(column_names, 1, len(column_names)-1) as [columns],
        case when i.[type] = 1 then 'Clustered index'
            when i.[type] = 2 then 'Nonclustered unique index'
            when i.[type] = 3 then 'XML index'
            when i.[type] = 4 then 'Spatial index'
            when i.[type] = 5 then 'Clustered columnstore index'
            when i.[type] = 6 then 'Nonclustered columnstore index'
            when i.[type] = 7 then 'Nonclustered hash index'
        end as index_type
    from sys.objects as t
        inner join sys.indexes as i
            on t.object_id = i.object_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 t.is_ms_shipped <> 1
    and index_id > 0
    order by schema_name(t.schema_id) + '.' + t.[name], i.index_id
    

    Columns

    • table_view - name of table or view index is defined for
    • object_type - type of object that index is defined for:
      • Table
      • View
    • index_id - id of index (unique in table)
    • type
      • Primary key
      • Unique
      • Not unique
    • index_name - index name
    • columns - list of index columns separated with ","
    • index_type - index type:
      • Clustered index
      • Nonclustered unique index
      • XML index
      • Spatial index
      • Clustered columnstore index
      • Nonclustered columnstore index
      • Nonclustered hash index

    Rows

    • One row: represents an index
    • Scope of rows: all indexes (unique and non-unique) in databases
    • Ordered by: schema, table name, index id

    Sample results

    0
    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.
    Accept