Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

A google search turns up some scripts that run a query against system tables and iterate over the the results rebuilding (or alternatively, reorganizing) each of your database table indexes in turn. This seems reasonable but the he reason I ask the question here is that taking SQL scripts from an unknown source that query system tables makes me nervous, e.g. maybe it will miss some kinds of index or try and rebuild special system indexes that should be left alone. As it happens the first couple I tried didn't work because they didn't handle tables in different schemas.

So, what is the consensus on the best and simplest way to rebuild all indexes across all tables for an SQL Server 2005 database?


share|improve this question

If you don't want to risk a third party script, the native maintenance plans include the option of rebuilding all of the indexes for each database.

share|improve this answer

Ola Hallegren has a mature & well written index optimize script for indexes which i can highly recommend. It uses the SQL Server 2005 ALTER INDEX REBUILD/REORGANIZE syntax which allows for online rebuilds in Enterprise.

share|improve this answer

As is often the case, BOL has a good starting point, with explanation of how it does what it does and what you can change to make it have different effects. I started with this script and added to it so that all databases are handled (on some servers). I also set a different value for @maxfrag depending on when the scrpt is running - weekends get a low value and weekdays get a higher one and whether there is a rebuild or a reorganize. This ensures the job runs within the time limits we have and that the database doesnt get a big lock

share|improve this answer

There's a really good "index defrag" script at which selectively rebuilds certain indices, reorganizes others, and leaves some alone.

This is all based on index fragmentation thresholds which you can specify in the script.

Very useful and extremely helpful indeed! You can just simply configure this script and then run it on all your databases.


share|improve this answer
Sqlfool's script runs against all databases by default, no need for sp_MSforeachDB. – SuperCoolMoss Nov 1 '09 at 18:02
Oh and I use this script on a number of Production instances - so can recommend it. – SuperCoolMoss Nov 1 '09 at 18:07

Not as complete/complex as sqlfool's script mentioned in other replies, but I use:

DECLARE curTables CURSOR FOR SELECT name FROM sysobjects WHERE xtype='U'
OPEN curTables
WHILE @@fetch_status = 0 BEGIN
    SET @sSQL = 'DBCC DBREINDEX ('''+@sName+''', '''', 0)'
    EXEC (@sSQL)
    FETCH NEXT FROM curTables INTO @sName
CLOSE curTables

to rebuild all indexes in the database currently logged into.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.