sp_fixindex

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).

Syntax

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]

Parameters

Examples

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