How to recreate an Index with the same settings?

Question

I'm in a situation where I want to add another included column to an existing index, but keep the rest of the settings (eg. FILLFACTOR, ONLINE, SORT_IN_TEMPDB, etc.) the same.

• I do not have the previous version in source control
• I didn't find most of them in sys.indexes DMV (FILLFACTOR is there, but not ONLINE)
• Finding the index in Object Explorer and scripting it out fills default values for the ONLINE setting (and omits DATA_COMPRESSION)
• I didn't find the property in the OBJECTPROPERTY DMF
• Brent Ozar's sp_BlitzIndex also doesn't provide some of these advanced settings
• sp_helpindex provides me with the same limited information as sp_help over the parent table.
• I cannot test if WITH (DROP_EXISTING = ON) recreates the index with the same settings, because I can't find the settings anywhere.

Any ideas?

Answer 1

You should probably play with scripting options in Tools-Options-SQL Server Object Explorer-Scripting in SSMS menu.

There you can define what you would like to script and what not. Compression is there.

As for the other properties - have a look at this StackOverflow question - it will probably shed some light:

SORT_IN_TEMPDB is not a property of an index, but rather an option that you can specify that says how the index is created. Once the index exists (i.e. the create index statement completes), whether or not SORT_IN_TEMPDB was specified at creation time isn't stored anywhere because it doesn't matter.