DbIndexes View - SQL Server

Overview¶

• This database view lists out all indexes in the database.
• The ColumnName field lists indexed columns in their key order. Columns included in the index but not the key (i.e., in a covering index) are enclosed in parentheses.
  

Code¶

/*==================================================================================================
    OBJECT: DbIndexes view
    SOURCE: http://jasinskionline.com/technicalwiki/DbIndexes-View-SQL-Server.ashx
==================================================================================================*/
create or alter view [dbo].[DbIndexes] as

select
     TableName      = o.name
    ,IndexName      = idx.Name
    ,idx.Is_Unique
    ,ColumnNames    = string_agg(
                            case when ic.is_included_column = 1 then '(' else '' end
                            + c.name
                            + case when ic.is_included_column = 1 then ')' else '' end
                            , ','
                            ) within group (order by ic.is_included_column, ic.key_ordinal)
from
    sys.indexes idx
    inner join sys.tables o on idx.object_id = o.object_id
    inner join sys.index_columns ic on idx.index_id = ic.index_id and idx.object_id = ic.object_id
    inner join sys.columns c on ic.column_id = c.column_id and c.object_id = o.object_id
group by
    o.name
    ,idx.name
    ,idx.Is_Unique