sp_fixindex now works on a set of indexes rather than on a single index. sp_fixindex rebuilds the data layer if the target table has a placement or clustered index (it reclaims the unused space in the data layer while working on the placement or clustered index of a system table).
This is the syntax for sp_fixindex (the new parameters are index_name and force_option):
sp_fixindex database_name, table_name [, index_id | null] [, index_name | null] [, force_option]
index_name – indicates the index that needs to be processed. If a NULL value is used, the index associated with index_id is rebuilt. If index_id is also a NULL value, all the indexes in the system table are rebuilt
force_option – forces Adaptive Server to rebuild the system table index in tempdb. sp_fixindex without the force_option forces the database specified by database_name to be in single-user mode, which is not possible for tempdb. Although the force_option allows you to rebuilt system catalogs in tempdb, it should not be used for user databases.
Example 1 Rebuilds the index with an index ID of 2 on testdb..sysprocedures:
sp_fixindex 'testdb', 'sysprocedures', 2
Example 2 Rebuilds the index csysprocedures in the testdb..sysprocedures system table:
sp_fixindex 'testdb', 'sysprocedures', null, 'csysprocedures'
Example 3 Rebuilds all available indexes on the sysprocedures table in testdb. If the table has clustered or placement index, sp_fixindex reclaims the unused space by removing the garbage present in data pages (that is, it rebuilds the data pages):
sp_fixindex 'testdb', 'sysprocedures'
Example 4 Rebuilds the index with an with an index ID of 2 on tempdb..sysprocedures:
sp_fixindex 'tempdb', 'sysprocedures', 2, null, 1
Example 5 Rebuilds the index csysprocedures for the table tempdb..sysprocedures:
sp_fixindex 'tempdb', 'sysprocedures', null, 'sysprocedures', 1
Example 6 Rebuilds all indexes on sysprocedures in tempdb:
sp_fixindex 'tempdb', 'sysprocedures', null, null, 1
