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.