Sean Lange (6/6/2013)
I for one would be super interested in seeing how you reverse engineer your indexes. I just went through this process and I have a feeling yours will be better than mine.
one of those things i keep hammering at now and then; and there was a lot of feedback to get this looking pretty:
take a look at this post, from a 2006! original thread where i posted how to do it SQL 2000 style, but this link is much more updated for 2008:
http://www.sqlservercentral.com/Forums/Topic401795-566-2.aspx#bm1079779
edit: no wait! that's an old cursor style post;
this is using a CTE and nice set based code:
With MyIndexBase
AS
(
SELECT
sys.schemas.schema_id, sys.schemas.[name] AS schema_name,
sys.objects.[object_id], sys.objects.[name] AS object_name,
sys.indexes.index_id, ISNULL(sys.indexes.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.[object_id], sys.indexes.[name], 'IndexDepth') AS IndexDepth,
sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor,
sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM
sys.objects
JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
JOIN sys.indexes ON sys.objects.[object_id]=sys.indexes.[object_id]
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
GROUP BY [object_id], index_id
) AS partitions ON sys.indexes.[object_id]=partitions.[object_id] AND sys.indexes.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=0
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
)
SELECT
CASE
WHEN is_primary_key = 0 or is_unique = 0
THEN 'CREATE INDEX [' + index_name + '] '
+ SPACE(128 - LEN(index_name))
+ ' ON [' + [object_name] + ']'
+ ' (' + index_columns_key + ')'
+ CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END
+ CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END
END ,*
from MyIndexBase
where [type_desc] != 'HEAP'
AND is_primary_key = 0 AND is_unique = 0
order by is_primary_key desc,is_unique desc
Lowell