Creating a job to re-index all user databases in an instance of SQL Server 2005/2008 Enterprise Edition

Here is a script that will create a job to re-index each user database in an instance that is not in read-only mode. The script uses a cursor to identify the available databases and creates a job step with a script to perform online reindexing on that database. The script is derived from an MSDN example script. It uses the dm_db_index_physical_stats function with the "limited" parameter to determine which indexes require defragmentation or rebuilding. Fragmentation thresholds and the minimum index size can be set. It ignores hypothetical and disabled indexes. Thanks to Geoff Hiten of the Atlanta.mdf user group who published changes to the original sample script to handle BLOB columns.

 The intended purpose of this script is to reduce the amount of time a DBA has to spend handling tasks such as this. The script implements push-button functionality to simplify the task of creating and updating index maintenance processes on a server.

I won't go into all the details about re-indexing. You can find that in MSDN and Books Online.

Posting updated January 9, 2009:

The DBID function does not work with a database running in SQL Server 2000 (8.0) compatible mode on 2005 or 2008. The solution is to explicitly pass in the database id value to the dm_db_index_physical_stats function. I am not going to update this script. If you want to see how it's done, please see my earlier post: Finding Fragmented Indexes in a Database Running in SQL Server 2000 Compatibility Mode on 2005.

CreateReindexJobSQLEnterprise_BlogPost.sql