here's another version i had slapped together once;
this is specific to SQL 2008 and above, becuase it's also scripting filtered indexes and include columns;
i never bothered fleshing out the partitions section of the scripts, but left the code in comments for reference.
if you run this on SQL 2005, you'll have to change it, as you get errors on the has_filter column and a few other things.
;WITH MyStagingData
AS
(
(SELECT
SCH.schema_id,
SCH.[name] COLLATE database_default AS SCHEMA_NAME,
OBJS.[object_id],
OBJS.[name] COLLATE database_default AS OBJECT_NAME,
IDX.index_id,
ISNULL(IDX.[name], '---') COLLATE database_default AS index_name,
partstatz.Rows,
partstatz.SizeMB,
INDEXPROPERTY(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type,
IDX.type_desc COLLATE database_default AS type_desc,
IDX.is_unique,
IDX.is_primary_key,
IDX.is_unique_constraint,
IDX.has_filter,
IDX.filter_definition,
ISNULL(Index_Columns.index_columns_key, '---') COLLATE database_default AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') COLLATE database_default AS index_columns_include,
IDX.is_padded,
IDX.fill_factor,
IDX.ignore_dup_key,
STATZ.no_recompute ,
IDX.allow_row_locks,
IDX.allow_page_locks,
PARTZ.data_compression_desc
FROM sys.objects OBJS
INNER JOIN sys.schemas SCH
ON OBJS.schema_id = SCH.schema_id
INNER JOIN sys.indexes IDX
ON OBJS.[object_id] = IDX.[object_id]
INNER JOIN (SELECT
[OBJECT_ID],
index_id,
SUM(row_count) AS ROWS,
CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(NUMERIC(19, 3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats STATS
GROUP BY
[OBJECT_ID],
index_id) AS partstatz
ON IDX.[object_id] = partstatz.[object_id]
AND IDX.index_id = partstatz.index_id
INNER JOIN sys.partitions PARTZ
ON IDX.[object_id] = PARTZ.[object_id]
AND IDX.index_id = PARTZ.index_id
INNER JOIN sys.stats STATZ
ON IDX.[object_id] = STATZ.[object_id]
AND IDX.index_id = STATZ.stats_id
CROSS APPLY (SELECT
LEFT(index_columns_key, LEN(index_columns_key) - 1) COLLATE database_default AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include) - 1) COLLATE database_default AS index_columns_include
FROM (SELECT
(SELECT
colz.[name] + CASE WHEN IXCOLS.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' + ' ' COLLATE database_default
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns colz
ON IXCOLS.column_id = colz.column_id
AND IXCOLS.[object_id] = colz.[object_id]
WHERE IXCOLS.is_included_column = 0
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY
key_ordinal
FOR XML PATH('')) AS index_columns_key,
(SELECT
colz.[name] + ',' + ' ' COLLATE database_default
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns colz
ON IXCOLS.column_id = colz.column_id
AND IXCOLS.[object_id] = colz.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY
index_column_id
FOR XML PATH('')) AS index_columns_include) AS Index_Columns) AS Index_Columns)
)
SELECT
'CREATE '
+ CASE WHEN MyStagingData.is_unique = 1 THEN ' UNIQUE' ELSE ' ' END
+ CASE WHEN MyStagingData.type_desc <> 'HEAP' THEN ' ' + MyStagingData.type_desc ELSE ' ' END
+ ' INDEX '
+ quotename(MyStagingData.index_name)
+ ' ON '
+ + quotename(MyStagingData.schema_name)
+'.'
+ quotename(MyStagingData.object_name)
+ ' (' + MyStagingData.index_columns_key + ')'
+ CASE WHEN MyStagingData.index_columns_include <> '---' THEN ' INCLUDE (' + MyStagingData.index_columns_include + ')' ELSE '' END
+ CASE WHEN MyStagingData.has_filter = 1 THEN ' WHERE ' + MyStagingData.filter_definition ELSE '' END
+ ' WITH '
+ ' PAD_INDEX = ' + CASE WHEN is_padded = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' FILLFACTOR = ' + convert(varchar,MyStagingData.fill_factor) + ','
+ ' IGNORE_DUP_KEY = ' + CASE WHEN ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' STATISTICS_NORECOMPUTE = ' + CASE WHEN no_recompute = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' ALLOW_ROW_LOCKS = ' + CASE WHEN allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' ALLOW_PAGE_LOCKS = ' + CASE WHEN allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' DATA_COMPRESSION = ' + data_compression_desc
-- + ','
--+ ' [ ON PARTITIONS ( { <partition_number_expression> | <range> } '
--+ ' [ , ...n ] ) ] '
,* FROM MyStagingData
WHERE MyStagingData.SCHEMA_NAME <> 'sys'
Lowell