Hi Lowell

I found 3 other things which could be interesting for you.

1) The column names in the index and constraint statements should be in squre brackets

for indexes where the column name is an reserved word (like 'table').

This can be easy done by inserting into the @result table

like

SELECT '[' + COLS.[name] + ']' + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '

2) Since you have scripted the index option "FILLFACTOR"

you may reconsider to script the "PAD_INDEX" too.

"PAD_INDEX" status is stored in sys.indexes (column "is_padded")

so it can be read out during the insert into the @result table.

3) An "unique index" (clustered or nonclustered) is scripted as an "unique constraint"

which is more o less a bug.

But it is easy to fix.

Orginal code (constraint part):

SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS

+ CASE

WHEN is_primary_key = 1 or is_unique = 1

THEN @vbCrLf

+ 'CONSTRAINT [' + index_name + '] '

+ SPACE(@STRINGLEN - LEN(index_name))

+ CASE

WHEN is_primary_key = 1

THEN ' PRIMARY KEY '

ELSE CASE

WHEN is_unique = 1

THEN ' UNIQUE '

ELSE ''

END

END

+ type_desc

+ CASE

WHEN type_desc='NONCLUSTERED'

THEN ''

ELSE ' '

END

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

+ CASE

WHEN dataspace_name <> '---'

THEN ' ON [' + dataspace_name + ']'

ELSE ''

END

ELSE ''

END + ','

FROM @RESULTS

WHERE [type_desc] != 'HEAP'

AND is_primary_key = 1

OR is_unique = 1

ORDER BY

is_primary_key DESC,

is_unique DESC

Corrected code (constraint part).

"is_unique" was replaced by "is_unique_constraint":

SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS

+ CASE

WHEN is_primary_key = 1 or is_unique_constraint = 1

THEN @vbCrLf

+ 'CONSTRAINT [' + index_name + '] '

+ SPACE(@STRINGLEN - LEN(index_name))

+ CASE

WHEN is_primary_key = 1

THEN ' PRIMARY KEY '

ELSE CASE

WHEN is_unique = 1

THEN ' UNIQUE '

ELSE ''

END

END

+ type_desc

+ CASE

WHEN type_desc='NONCLUSTERED'

THEN ''

ELSE ' '

END

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

+ CASE

WHEN dataspace_name <> '---'

THEN ' ON [' + dataspace_name + ']'

ELSE ''

END

ELSE ''

END + ','

FROM @RESULTS

WHERE [type_desc] != 'HEAP'

AND (is_primary_key = 1

OR is_unique_constraint = 1)

You yould go a step further and remove the outher "case when" statement which is not necessary

this will make the code shorter and easier to read.

SELECT @CONSTRAINTSQLS += @vbCrLf

+ 'CONSTRAINT [' + index_name + '] '

+ SPACE(@STRINGLEN - LEN(index_name))

+ CASE

WHEN is_primary_key = 1

THEN ' PRIMARY KEY '

ELSE CASE

WHEN is_unique = 1

THEN ' UNIQUE '

ELSE ''

END

END

+ type_desc

+ CASE

WHEN type_desc='NONCLUSTERED'

THEN ''

ELSE ' '

END

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

+ CASE

WHEN dataspace_name <> '---'

THEN ' ON [' + dataspace_name + ']'

ELSE ''

END

+ ','

FROM @RESULTS

WHERE [type_desc] != 'HEAP'

AND (is_primary_key = 1

OR is_unique_constraint = 1)

Orginal code (index part):

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

+ CASE

WHEN dataspace_name <> '---'

THEN ' ON [' + dataspace_name + ']'

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

Corrected code (index part).

"is_unique" was replaced by "is_unique_constraint"

AND the "or" in the "where condition" and the "when condition" was replaced by "and"

SELECT @INDEXSQLS = @INDEXSQLS

+ CASE

WHEN is_primary_key = 0 and is_unique_constraint = 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

+ CASE

WHEN dataspace_name <> '---'

THEN ' ON [' + dataspace_name + ']'

ELSE ''

END

END

FROM @RESULTS

WHERE [type_desc] != 'HEAP'

AND is_primary_key = 0

AND is_unique_constraint = 0

ORDER BY

is_primary_key DESC,

is_unique DESC

In "index part" statement you can also remove the outer "case when" if you want.

Here is a query to check if you have any unique indexes in your system.

select *

from sys.indexes i

inner join sys.objects o

on i.object_id = o.object_id

where i.is_unique = 1

and i.is_unique_constraint = 0

and i.is_primary_key = 0

and o.type = 'U'

Here is some small code to test the 3 issues.

Create the table and compare the management studio code

with the code of your procedure.

CREATE TABLE [dbo].[test](

varchar(10) NOT NULL)

CREATE UNIQUE NONCLUSTERED INDEX index_test ON [dbo].test

(

ASC

)WITH (PAD_INDEX = ON, FILLFACTOR = 20) ON [INDEX]

CREATE UNIQUE CLUSTERED INDEX index_test2 ON [dbo].test

(

ASC

)WITH (PAD_INDEX = ON, FILLFACTOR = 20) ON [INDEX]

Nikus