The complete version for SQL2005/2008 is below. It includes schema... plenty of monkeys use multiple schemas..
Enjoy.
SET NOCOUNT ON
DECLARE
@table_schema_name nvarchar(128),
@table_name nvarchar(128),
@index_name nvarchar(128),
@key_ordinal smallint,
@column_name nvarchar(128),
@sort_order varchar(50),
@is_included_column int,
@index_type_desc varchar(50),
@unique_type varchar(50),
@ignore_dup_key varchar(50),
@fill_factor tinyint,
@is_padded varchar(50),
@allow_row_locks varchar(50),
@allow_page_locks varchar(50),
-------------------------------------------
@table_schema_name_ws nvarchar(128),
@table_name_ws nvarchar(128),
@index_name_ws nvarchar(128),
@key_ordinal_ws smallint,
-------------------------------------------
@text1_create varchar(800),
@text1_create_columns varchar(800),
@text1_create_end varchar(50),
@text2_include_start varchar(800),
@text2_include_columns varchar(50),
@text2_include_end varchar(50),
@text4_with varchar(800),
@text4_with_end varchar(50)
SET @text1_create_end = ')'
SET @text2_include_start = 'INCLUDE ('
SET @text2_include_end = ')'
SET @text4_with_end = ')'
DECLARE C01 CURSOR FOR
SELECT
S.name AS table_schema_name,
O.name AS table_name,
I.name AS index_name,
CASE IC.key_ordinal WHEN 0 THEN 999 ELSE IC.key_ordinal END AS key_ordinal,
C.name AS column_name,
CASE IC.is_descending_key WHEN 0 THEN 'ASC' ELSE 'DESC' END AS sort_order,
IC.is_included_column,
I.type_desc AS index_type_desc,
CASE I.is_unique WHEN 1 THEN 'UNIQUE' ELSE '' END AS unique_type,
CASE I.ignore_dup_key WHEN 1 THEN 'ON' ELSE 'OFF' END AS ignore_dup_key,
I.fill_factor,
CASE I.is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END AS is_padded,
CASE I.allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_row_locks,
CASE I.allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_page_locks
FROM
sys.indexes I
INNER JOIN
sys.objects O
ON O.object_id = I.object_id AND O.type_desc = 'USER_TABLE'
INNER JOIN
sys.schemas S
ON S.schema_id = O.schema_id
INNER JOIN
sys.data_spaces D
ON D.data_space_id = I.data_space_id AND D.name = 'PRIMARY'
INNER JOIN
sys.index_columns IC
ON IC.object_id = I.object_id AND IC.index_id = I.index_id
INNER JOIN
sys.columns C
ON C.object_id = IC.object_id AND C.column_id = IC.column_id
WHERE
I.type_desc IN ('NONCLUSTERED')
AND I.is_hypothetical = 0
ANDI.is_primary_key = 0-- Exclude primary key constraints
AND I.is_unique_constraint = 0-- Exclude unique constraints
AND I.is_disabled <> 1-- Exclude disabled indexes
ORDER BY 1, 2, 3, 4
OPEN C01
FETCH C01 INTO
@table_schema_name,
@table_name,
@index_name,
@key_ordinal,
@column_name,
@sort_order,
@is_included_column,
@index_type_desc,
@unique_type,
@ignore_dup_key,
@fill_factor,
@is_padded,
@allow_row_locks,
@allow_page_locks
SELECT @table_schema_name_ws = @table_schema_name
SELECT @table_name_ws = @table_name
SELECT @index_name_ws = @index_name
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE @@FETCH_STATUS = 0
AND @table_schema_name_ws = @table_schema_name
BEGIN
WHILE @@FETCH_STATUS = 0
AND @table_schema_name_ws = @table_schema_name
AND @table_name_ws = @table_name
BEGIN
SET @text1_create_columns = ''
SET @text2_include_columns = ''
'DROP INDEX [' +
@index_name_ws + '] ON [' +
@table_schema_name_ws + '].[' +
@table_name_ws + '];'
PRINT ' '
SELECT @text1_create =
'CREATE ' +
@unique_type +
' ' + @index_type_desc +
' INDEX [' +
@index_name_ws + '] ON [' +
@table_schema_name_ws + '].[' +
@table_name_ws + '] ('
SELECT @text4_with =
'WITH (PAD_INDEX = ' + @is_padded + ', STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ' +
'IGNORE_DUP_KEY = ' + @ignore_dup_key + ', ONLINE = OFF, ' +
'FILLFACTOR = ' + CAST(@fill_factor AS VARCHAR(50)) + ', ' +
'ALLOW_ROW_LOCKS = ' + @allow_row_locks + ', ' +
'ALLOW_PAGE_LOCKS = ' + @allow_page_locks
WHILE @@FETCH_STATUS = 0
AND @table_schema_name_ws = @table_schema_name
AND @table_name_ws = @table_name
AND @index_name_ws = @index_name
BEGIN
IF @is_included_column = 1
BEGIN
SELECT @text2_include_columns = @text2_include_columns + @column_name + ','
END
ELSE
BEGIN
SELECT @text1_create_columns = @text1_create_columns + @column_name + ','
END
FETCH C01 INTO
@table_schema_name,
@table_name,
@index_name,
@key_ordinal,
@column_name,
@sort_order,
@is_included_column,
@index_type_desc,
@unique_type,
@ignore_dup_key,
@fill_factor,
@is_padded,
@allow_row_locks,
@allow_page_locks
END
IF @@FETCH_STATUS = 0 SELECT @index_name_ws = @index_name
SELECT @text1_create_columns = '' + SUBSTRING(@text1_create_columns, 1, (DATALENGTH(@text1_create_columns) - 1))
PRINT @text1_create
PRINT @text1_create_columns
PRINT @text1_create_end
IF DATALENGTH(@text2_include_columns) > 0
BEGIN
PRINT @text2_include_start
PRINT '' + SUBSTRING(@text2_include_columns, 1, (DATALENGTH(@text2_include_columns) - 1))
PRINT @text2_include_end
END
PRINT @text4_with
PRINT @text4_with_end
PRINT 'ON INDEX;'
PRINT '----'
END
IF @@FETCH_STATUS = 0 SELECT @table_name_ws = @table_name
END
IF @@FETCH_STATUS = 0
SELECT @table_schema_name_ws = @table_schema_name
END
CLOSE C01
DEALLOCATE C01