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?

• McNets: Have you tried with INDEXPROPERTY?
• Zikato: @McNets I have not as I was not aware of it :). But reading the documentation it doesn't provide the properties I mentioned in my post (eg. ONLINE).
• Scott Hodgin - Retired: I don't think things like ONLINE and SORT_IN_TEMPDB are properties of an index, but more like instructions on how to build/rebuild the index. Once the index is built, those options are meaningless.
• Randi Vertongen: FYI DROP_EXISTING = ON removes settings such as a custom FILL FACTOR

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.