Hi
I added another small improvment.
The keyword "clustered" or "nonclustered" in the index creation statement.
Without this all indexes are created as nonclustered indexes which is in the most cases correct.
But theoreticly it is possible to create a clustered index which ist not a PK constraint.
For such an index this keyword is necessary.
Look for:
--##############################################################################
--indexes
--##############################################################################
SELECT @INDEXSQLS = @INDEXSQLS
+ CASE
WHEN is_primary_key = 0 or is_unique = 0
THEN @vbCrLf
+ 'CREATE INDEX [' + index_name + '] '
+ SPACE(@STRINGLEN - 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 @RESULTS
WHERE [type_desc] != 'HEAP'
AND is_primary_key = 0
AND is_unique = 0
ORDER BY
is_primary_key DESC,
is_unique DESC
and replace it with:
SELECT @INDEXSQLS = @INDEXSQLS
+ CASE
WHEN is_primary_key = 0 or is_unique = 0
THEN @vbCrLf
+ 'CREATE ' + type_desc + ' INDEX [' + index_name + '] '
+ SPACE(@STRINGLEN - 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 @RESULTS
WHERE [type_desc] != 'HEAP'
AND is_primary_key = 0
AND is_unique = 0
ORDER BY
is_primary_key DESC,
is_unique DESC
Just the "CREATE INDEX" line was changed.
Nikus