Query to list SQL Server indexes in a specific database

Sometimes you need to know the list of SQL Server indexes available in the SQL database. There are many ways to get the list of indexes. It is my query to list SQL Server indexes in a specific database. The query returns the indexes' name, type, and key columns.

The following T-SQL script is to list SQL Server indexes in a specific database. You can supply the database name in the parameter @DBName, and it returns the list of indexes.

Declare @DBName varchar(100)='AdventureWorks2019';
Declare @sql varchar(max)='
select
schema_name(t.schema_id) + ''.'' + t.[name] as table_view, 
 si.[name] as index_name,
    case when t.[type] = ''U'' then ''Table''
        when t.[type] = ''V'' then ''View''
        end as [object_type],
   substring(column_names, 1, len(column_names)-1) as [columns],
          case when si.[type] = 1 then ''Clustered index''
        when si.[type] = 2 then ''Nonclustered unique index''
        when si.[type] = 3 then ''XML index''
        when si.[type] = 4 then ''Spatial index''
        when si.[type] = 5 then ''Clustered columnstore index''
        when si.[type] = 6 then ''Nonclustered columnstore index''
        when si.[type] = 7 then ''Nonclustered hash index''
        end as index_type
    
from sys.objects t
    inner join sys.indexes si
        on t.object_id = si.object_id
    cross apply (select col.[name] + '', ''
                    from sys.index_columns ic
                       inner join sys.columns 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 = si.index_id
                           order by key_ordinal
                           for xml path ('''') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0
order by si.[name]'
exec ('USE ' + @DBName + @sql)

I got the following SQL Server Indexes list for the [AdventureWorks2019] sample database.