I made a little change to the last script to also include the where clause of a filtered index. I also removed the collate because this didn't work for me.
--1. get all indexes from current db, place in temp table
select
tablename = schemas.name + '].[' + object_name(i.object_id),
tableid = i.object_id,
indexid = i.index_id,
indexname = i.name,
isunique = indexproperty (i.object_id,i.name,'isunique'),
isclustered = indexproperty (i.object_id,i.name,'isclustered'),
indexfillfactor = indexproperty (i.object_id,i.name,'indexfillfactor'),
CASE WHEN i.filter_definition IS NULL THEN '' ELSE ' WHERE '+i.filter_definition END Filter_Definition
into #tmp_indexes
from sys.indexes i
JOIN sys.objects ON i.object_id = objects.object_id
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
where i.index_id > 0 and i.index_id < 255 --not certain about this
--AND INDEXPROPERTY (i.object_id,i.name,'ISUNIQUE') = 0 --comment out to include unique and
AND INDEXPROPERTY (i.object_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), 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 Filter_Definition
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN Filter_Definition + ' WITH (ONLINE = ON)'
WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN Filter_Definition + ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') '+Filter_Definition+' WITH (ONLINE = ON)'
ELSE ' INCLUDE(' + INCLUDES + ') '+Filter_Definition+' 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
--makes the drop
SELECT 'DROP INDEX '
+' [' + tablename + '].['
+ indexname + ']'
FROM #tmp_indexes where left(tablename,3) not in ('sys', 'dt_')
--Drop the temp table again
DROP TABLE #tmp_indexes
set nocount off