Index options and applying partitioning to an index I have following two questions:


I have a SQL Server index with the following options:

PAD_INDEX = OFF
STATISTICS_NORECOMPUTE = OFF
SORT_IN_TEMPDB = OFF
DROP_EXISTING = OFF
ONLINE = OFF
ALLOW_ROW_LOCKS = ON
ALLOW_PAGE_LOCKS = ON
FILLFACTOR = 70


Where are these details are stored in the database? I have found tables like sys.indexes and sys.stats where a few of these options are stored.  I need the complete list, if possible.
To partition an index in SQL Server, we need to drop it and recreate it by associating it with partition scheme.  Is this the only way to apply partition on an existing index?
Not all index create options are persisted as meta-data; these (e.g. SORT_IN_TEMPDB, ONLINE, etc.) are specified only as run-time DDL options.

Rather than drop and recreate the index entirely, an index can be partitioned by using CREATE INDEX...WITH (DROP_EXISTING=ON).

CREATE INDEX YourTable_Index ON dbo.YourTable(IndexColumn)
    WITH (DROP_EXISTING=ON) ON PS_YourTable(PartitioningColumn);


EDIT:

Be aware that SQL Server does not consider the existing index FILLFACTOR with CREATE INDEX...DROP_EXISTING=ON because a new index is created. Although, the existing index FILLFACTOR can be used with ALTER INDEX...REBUILD (by omitting the FILLFACTOR clause), that method does not allow changing partitioning or the filegroup whereas CREATE INDEX...DROP_EXISTING=ON does and avoids sorting the rows during the index recreate. The desired FILLFACTOR needs to be explicitly specified when using CREATE INDEX...DROP_EXISTING=ON when a non-default value is desired.

You'll need to generate the DDL dynamically if you want to retain the old index FILLFACTOR (and other persisted options of the original index) without manually specifying the value.