--------------------------------------------------------------------

declare
@object_id          int,
@index_id           tinyint,
@schema_name        sysname,
@table_name         sysname,
@index_name         sysname,
@type               tinyint,
@uniqueness         bit,
@indexed_column     sysname,
@included_column    sysname,
@indexed_columns    varchar(max),
@included_columns   varchar(max),
@has_included_cols  bit,
@is_descending_key  bit,
@stmt               varchar(max),
@crlf               char(2)

set @crlf = char(13) + char(10)

declare indexes cursor
for
select 
    schema_name     = s.name,
    table_name      = t.name,
    index_id        = i.index_id,
    index_name      = i.name,
    type            = i.type,
    uniqueness      = i.is_unique
from 
         sys.schemas            s
    join sys.tables             t   on s.schema_id = t.schema_id
    join sys.indexes            i   on t.object_id = i.object_id
where
    i.type > 0  -- none -heap
order 
    by s.name,
    t.name,
    i.index_id

open indexes

fetch 
    indexes
into
    @schema_name,
    @table_name ,
    @index_id   ,
    @index_name ,
    @type       ,
    @uniqueness 

while @@fetch_status<>(-1)
begin

    select @object_id = object_id(@schema_name + '.' + @table_name)
    set @indexed_columns = '('

    declare indexed_columns cursor
    for
    select 
        c.name,
        ic.is_descending_key
    from
             sys.index_columns  ic
        join sys.columns        c   on ic.column_id = c.column_id
                                   and ic.object_id = c.object_id
    where 
        ic.object_id = @object_id
        and ic.index_id = @index_id
        and ic.is_included_column = 0
    order by 
        ic.index_column_id

    open indexed_columns

    fetch indexed_columns
    into @indexed_column, @is_descending_key

    while @@fetch_status<>(-1)
    begin

        set @indexed_columns = @indexed_columns + @indexed_column +
                               case @is_descending_key when 1 then ' desc ' else '' end + ', '

        fetch indexed_columns
        into @indexed_column, @is_descending_key

    end

    close indexed_columns
    deallocate indexed_columns

    set @indexed_columns = left(@indexed_columns, len(@indexed_columns)-1) + ')'

    if exists
           (select  object_id
            from    sys.index_columns
            where   object_id = @object_id
                    and index_id = @index_id
                    and is_included_column = 1 )
    begin
        set @included_columns = 'include ('

        declare included_columns cursor
        for
        select 
            c.name,
            ic.is_descending_key
        from
                 sys.index_columns  ic
            join sys.columns        c   on ic.column_id = c.column_id
                                       and ic.object_id = c.object_id
        where 
            ic.object_id = @object_id
            and ic.index_id = @index_id
            and ic.is_included_column = 1
        order by 
            ic.index_column_id

        open included_columns

        fetch included_columns
        into @included_column, @is_descending_key

        while @@fetch_status<>(-1)
        begin

            set @included_columns = @included_columns + @included_column +
                                    case @is_descending_key when 1 then ' desc ' else '' end + ', '

            fetch included_columns
            into @included_column, @is_descending_key

        end

        close included_columns
        deallocate included_columns

        set @included_columns = left(@included_columns, len(@included_columns)-1) + ')' + @crlf

    end

    set @stmt = 
    'create ' + 
    case @uniqueness when 1 then 'unique ' else '' end +
    case @type when 1 then 'clustered ' else '' end +
    'index ' + @index_name + @crlf +
    'on ' + @schema_name + '.' + @table_name + @indexed_columns + @crlf + 
    isnull(@included_columns,'') + 
    'g' + 'o' + @crlf + @crlf

    print @stmt

    fetch 
        indexes
    into
        @schema_name,
        @table_name ,
        @index_id   ,
        @index_name ,
        @type       ,
        @uniqueness 

end

close indexes
deallocate indexes

--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