Index Information in Particular Database

Declare @servername sysname, @dbname sysname

SELECT @servername = @@SERVERNAME

SELECT @dbname = DB_NAME();

with connect(schema_name,table_name,index_name,index_column_id,column_name) as

(   select s.name schema_name, t.name table_name, i.name index_name, index_column_id, cast(c.name as varchar(max)) column_name

 from sys.tables t

inner join sys.schemas s on t.schema_id = s.schema_id

inner join sys.indexes i on i.object_id = t.object_id

inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id

        inner join sys.columns c on c.object_id = t.object_id and

                ic.column_id = c.column_id

                where index_column_id=1

union all

select s.name schema_name, t.name table_name, i.name index_name, ic.index_column_id, cast(connect.column_name + ',' + c.name as varchar(max)) column_name

 from sys.tables t

inner join sys.schemas s on t.schema_id = s.schema_id

inner join sys.indexes i on i.object_id = t.object_id

inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id

        inner join sys.columns c on c.object_id = t.object_id and

                ic.column_id = c.column_id join connect on

connect.index_column_id+1 = ic.index_column_id

and connect.schema_name = s.name

and connect.table_name = t.name

and connect.index_name = i.name)

select @servername AS ServerName,@dbname AS DBName,connect.schema_name,connect.table_name,connect.index_name,connect.column_name

from connect join (select schema_name,table_name,index_name,MAX(index_column_id) index_column_id

from connect group by schema_name,table_name,index_name) mx

on connect.schema_name = mx.schema_name

and connect.table_name = mx.table_name

and connect.index_name = mx.index_name

and connect.index_column_id = mx.index_column_id

order by 3,4,5

go