wow this script has come a long way since the original version; thanks again to everyone who has contributed;

I cleaned up the formatting a little bit and added some comments, so that folks run this against SQL 2005 don't panic when it fails due to the new filtered indexes;

--Script SQL 2008+ Indexes AS CREATE INDEX Statements

--http://www.sqlservercentral.com/Forums/Topic401795-566-1.aspx

--original version submitted by anonymous/unknown contributor

--enhanced and contributed to discussion by Lowell

--enhanced by thorv-918308 to correct index columns in the correct sequence

--enhanced by thorv-918308 added fill factor, and with ONLINE = ON

--enhanced by mfuller333 to include DROP statements!

--enhanced by Sander A. to include filtered indexes!

--enhanced by craigborri to put the clustered indexes first, you'll want to do if you ever run this for real

/*if you get this error:

Msg 207, Level 16, State 1, Line 22

Invalid column name 'filter_definition'.

Msg 207, Level 16, State 1, Line 24

Invalid column name 'filter_definition'.

Msg 208, Level 16, State 0, Line 11

Invalid object name '#tmp_indexes'.

the issue is the database in question is SQL 2005: filter_definition is SQL 2008

see the inline code to comment/uncomment to make this SQL 2005 compliant.

*/

--################################################################################################

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

--################################################################################################

SELECT

tablename = QUOTENAME(scmz.name) + '.' + QUOTENAME((OBJECT_NAME(ixz.object_id))),

tableid = ixz.object_id,

indexid = ixz.index_id,

indexname = ixz.name,

isunique = INDEXPROPERTY (ixz.object_id,ixz.name,'isunique'),

isclustered = INDEXPROPERTY (ixz.object_id,ixz.name,'isclustered'),

indexfillfactor = INDEXPROPERTY (ixz.object_id,ixz.name,'indexfillfactor'),

--SQL2008+ Filtered indexes:

CASE

WHEN ixz.filter_definition IS NULL

THEN ''

ELSE ' WHERE ' + ixz.filter_definition

END Filter_Definition

--For 2005, which did not have filtered indexes, comment out the above CASE statement, and uncomment this:

--'' AS Filter_Definition

INTO #tmp_indexes

FROM sys.indexes ixz

INNER JOIN sys.objects obz

ON ixz.object_id = obz.object_id

INNER JOIN sys.schemas scmz

ON obz.schema_id = scmz.schema_id

WHERE ixz.index_id > 0

AND ixz.index_id < 255 ---- 0 = HEAP index, 255 = TEXT columns index

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

AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0 -- comment out to 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

--################################################################################################

--Cursor Block

--################################################################################################

OPEN index_cursor

FETCH NEXT FROM index_cursor INTO @tableid, @indexid

WHILE @@FETCH_STATUS <> -1

BEGIN

SELECT @isql_key = '', @isql_incl = ''

SELECT --ixz.name, colz.colid, colz.name, ixcolz.index_id, ixcolz.object_id, *

--key column

@isql_key = CASE ixcolz.is_included_column

WHEN 0

THEN CASE ixcolz.is_descending_key

WHEN 1

THEN @isql_key + COALESCE(colz.name,'') + ' DESC, '

ELSE @isql_key + COALESCE(colz.name,'') + ' ASC, '

END

ELSE @isql_key

END,

--include column

@isql_incl = CASE ixcolz.is_included_column

WHEN 1

THEN CASE ixcolz.is_descending_key

WHEN 1

THEN @isql_incl + COALESCE(colz.name,'') + ', '

ELSE @isql_incl + COALESCE(colz.name,'') + ', '

END

ELSE @isql_incl

END

FROM sysindexes ixz

INNER JOIN sys.index_columns AS ixcolz

ON (ixcolz.column_id > 0

AND ( ixcolz.key_ordinal > 0

OR ixcolz.partition_ordinal = 0

OR ixcolz.is_included_column != 0)

)

AND ( ixcolz.index_id=CAST(ixz.indid AS INT)

AND ixcolz.object_id=ixz.id

)

INNER JOIN sys.columns AS colz

ON colz.object_id = ixcolz.object_id

AND colz.column_id = ixcolz.column_id

WHERE ixz.indid > 0 AND ixz.indid < 255

AND (ixz.status & 64) = 0

AND ixz.id = @tableid

AND ixz.indid = @indexid

ORDER BY

ixz.name,

CASE ixcolz.is_included_column

WHEN 1

THEN ixcolz.index_column_id

ELSE ixcolz.key_ordinal

END

--remove any trailing commas from the cursor results

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)

--put the columns collection into our temp table

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

--################################################################################################

--End Cursor Block

--################################################################################################

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 for results-to-text

SELECT '---------------------------------------------------------------------'

--create index scripts (for backup)

SELECT 'IF NOT EXISTS(SELECT OBJECT_ID(' + INDEXNAME + ')' + ' ' +

'CREATE '

+ CASE WHEN ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END

+ CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END

+ 'INDEX ' + QUOTENAME(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

ISCLUSTERED desc,

tablename,

indexid,

indexname

--makes the drop

SELECT

'DROP INDEX '

+ ' ' + (tablename) + '.'

+ (indexname) + ''

FROM #tmp_indexes

WHERE LEFT(tablename,4) NOT IN ('[sys', 'dt_')

--Drop the temp table again

DROP TABLE #tmp_indexes

SET NOCOUNT OFF