Nice procedure, saves me a lot of work.

Thanks for sharing it.

I have made some small improvement to add the order (asc,desc) of the index columns.

Maybe you want to add it in your code too.

Look for:

SELECT COLS.[name] + ',' + ' '

FROM sys.index_columns IXCOLS

INNER JOIN sys.columns COLS

ON IXCOLS.column_id = COLS.column_id

AND IXCOLS.[object_id] = COLS.[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,

and replace it with:

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

FROM sys.index_columns IXCOLS

INNER JOIN sys.columns COLS

ON IXCOLS.column_id = COLS.column_id

AND IXCOLS.[object_id] = COLS.[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,

Just the first line (select part) was changed,

the rest of the code ist for easier finding.

Thanks again.