WITH    IndexList
          AS ( select   schema_name(sys.objects.schema_id) as SchemaName,
                        sys.objects.[name] as TableName,
                        sys.columns.[name] as ColumnName,
                        Indexes.IndexName as IndexName,
                        Composite = case Indexes.IsComposite
                                      when 0 then 'NO'
                                      when 1 then 'YES'
                                      else 'unknown'
                                    end
               from     sys.objects
                        inner join sys.columns on ( sys.columns.[object_id] = sys.objects.[object_id] )
                        inner join ( select sys.indexes.[name] as IndexName,
                                            IsComposite = case ( select count(*)
                                                                 from   sys.indexes CtrlNdx
                                                                        inner join sys.index_columns CtrlNdxCol on ( CtrlNdxCol.[object_id] = CtrlNdx.[object_id] )
                                                                                                                   and ( CtrlNdxCol.index_id = CtrlNdx.index_id )
                                                                 where  ( CtrlNdx.index_id = sys.indexes.index_id )
                                                                        and ( CtrlNdx.[object_id] = sys.objects.[object_id] )
                                                               )
                                                            when 1 then 0
                                                            else 1
                                                          end,
                                            sys.indexes.[object_id] as [object_id],
                                            sys.index_columns.column_id as column_id
                                     from   sys.indexes
                                            inner join sys.index_columns on ( sys.index_columns.[object_id] = sys.indexes.[object_id] )
                                                                            and ( sys.index_columns.index_id = sys.indexes.index_id )
                                            inner join sys.objects on ( sys.objects.[object_id] = sys.indexes.[object_id] )
                                            inner join sys.columns on ( sys.columns.[object_id] = sys.objects.[object_id] )
                                                                      and ( sys.columns.column_id = sys.index_columns.column_id )
                                   ) Indexes on ( Indexes.[object_id] = sys.objects.[object_id] )
                                                and ( Indexes.column_id = sys.columns.column_id )
               where    ( sys.objects.[type] = 'U' )
                        and ( sys.objects.[name] not in ( 'sysdiagrams' ) )
             )
    SELECT DISTINCT
            tablename,
            indexname
    FROM    IndexList