Nice script...

Added back the ability to include or exclude PK and Unique indexes, as well as schema if your not using the default dbo. Also now creates the drop statement as well.

--1. get all indexes from current db, place in temp table

select

tablename = schemas.name + '].[' + 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

JOIN sys.objects ON i.id = objects.object_id

JOIN sys.schemas ON objects.schema_id = schemas.schema_id

where i.indid > 0 and i.indid < 255 --not certain about this

and (i.status & 64) = 0

AND INDEXPROPERTY (i.id,i.name,'ISUNIQUE') =0 --comment out to include unique and

AND INDEXPROPERTY (i.id,i.name,'ISCLUSTERED') =0 --include PK's

--add additional columns to store include and key column lists

alter table #tmp_indexes add keycolumns varchar(4000) COLLATE Latin1_General_BIN2, includes varchar(4000)COLLATE Latin1_General_BIN2

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)COLLATE Latin1_General_BIN2 not in ('sys', 'dt_') --exclude system tables

order by tablename, indexid, indexname COLLATE Latin1_General_BIN2

set nocount off

--drop table #tmp_indexes

--makes the drop

SELECT 'DROP INDEX '

+' [' + tablename + '].['

+ indexname + ']'

FROM #tmp_indexes where left(tablename,3)COLLATE Latin1_General_BIN2 not in ('sys', 'dt_')