--1. get all indexes from current db, place in temp table
select
        tablename = object_name(i.id),
        tableid = i.id,
        indexid = i.indid,
        indexname = i.name,
        i.status,
        isunique = indexproperty (i.id,i.name,'isunique'),
        isclustered = indexproperty (i.id,i.name,'isclustered'),
        indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')
into #tmp_indexes
from sysindexes i
where i.indid > 0 and i.indid < 255                                             --not certain about this
and (i.status & 64) = 0                                                                 --existing indexes


--add additional columns to store include and key column lists
alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)
go
--################################################################################################




--2. loop through tables, put include and index columns into variables
declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int

declare index_cursor cursor for
select tableid, indexid from #tmp_indexes  

open index_cursor
fetch next from index_cursor into @tableid, @indexid

while @@fetch_status <> -1
begin

        select @isql_key = '', @isql_incl = ''

        select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *
                --key column
                @isql_key = case ic.is_included_column 
                        when 0 then 
                                case ic.is_descending_key 
                                        when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '
                                        else            @isql_key + coalesce(sc.name,'') + ' ASC, '
                                end
                        else @isql_key end,
                        
                --include column
                @isql_incl = case ic.is_included_column 
                        when 1 then 
                                case ic.is_descending_key 
                                        when 1 then @isql_incl + coalesce(sc.name,'') + ', ' 
                                        else @isql_incl + coalesce(sc.name,'') + ', ' 
                                end
                        else @isql_incl end
        from sysindexes i
        INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id)
        INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id
        
        where i.indid > 0 and i.indid < 255
        and (i.status & 64) = 0
        and i.id = @tableid and i.indid = @indexid
        order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end

        
        if len(@isql_key) > 1   set @isql_key   = left(@isql_key,  len(@isql_key) -1)
        if len(@isql_incl) > 1  set @isql_incl  = left(@isql_incl, len(@isql_incl) -1)

        update #tmp_indexes 
        set keycolumns = @isql_key,
                includes = @isql_incl
        where tableid = @tableid and indexid = @indexid

        fetch next from index_cursor into @tableid,@indexid
        end

close index_cursor
deallocate index_cursor

--remove invalid indexes,ie ones without key columns
delete from #tmp_indexes where keycolumns = ''
--################################################################################################




--3. output the index creation scripts
set nocount on

--separator
select '---------------------------------------------------------------------'

--create index scripts (for backup)
SELECT  
        'CREATE ' 
        + CASE WHEN ISUNIQUE    = 1 THEN 'UNIQUE ' ELSE '' END 
        + CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END 
        + 'INDEX [' + INDEXNAME + ']' 
        +' ON [' + TABLENAME + '] '
        + '(' + keycolumns + ')' 
        + CASE 
                WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN '' 
                WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)' 
                WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
                WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)'
                ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'  
        END
FROM #tmp_indexes
where left(tablename,3) not in ('sys', 'dt_')   --exclude system tables
order by tablename, indexid, indexname


set nocount off

--drop table #tmp_indexes

We walk in the dark places no others will enter
We stand on the bridge and no one may pass