Reorganize Indexes T-SQL

Question

I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on View T-SQL I noticed that it generates the T-SQL on the fly for all the indexes in the Database. If I copy the generated T-SQL I will have to maintain it every time I add a new Index.

Is there a way to do the same that the Task is doing by using T-SQL? Iterating over all indexes and creating the T-SQL for Rebuilding the index on the fly?

Answer 1

I recommend either of the following:

• Michell Ufford's index defragmentation script
• Ola Hallengren's index and statistics maintenance script

Either of these can accomplish the task of maintaining indexes without requiring you to rebuild the scripts when indexes are added or removed.

One of the biggest problems I've seen with using the maintenance plan is the fact that it appears to iterate through every single index and place the index rebuild into a single script. Then the script is compiled and finally run.

Now imagine that you have a 150 databases each with over 1000 indexes. This becomes a maintenance plan that takes 8 hours to run.

Answer 2

To answer my own question.

Since I'm doing this on a Worker Role, i'm first querying for all the indices using the T-SQL from this blog, storing the information on a list and creating the SQL with a StringBuilder and a for each loop.