SQL Server script to rebuild all indexes for all tables and all databasesWritten By: Edgewood Solutions Engineers -- 11/6/2007 -- 4 commentsStay info......
SQL Server script to rebuild all indexes for all tables and all databases
Written By: Edgewood Solutions Engineers -- 11/6/2007 -- 4 comments     
|
Stay informed - get the MSSQLTips.com newsletter and win - click here
|
Problem
One of the main functions of a DBA is to maintain database indexes. There have been several tips written about different commands to use for both index rebuilds and index defrags as well as the differences between index maintenance with SQL Server 2000 and SQL Server 2005. In addition, other tips have been written about using maintenance plans to maintain indexes on all databases. One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not. What other options are there to rebuild indexes on all databases besides using a maintenance plan?
Solution
The one nice thing about maintenance plans is that it works across multiple databases and therefore you can push out one task to handle the same activity across all of your databases. The problem that I have seen with maintenance plans though is that sometimes they do not work as expected, therefore here is another approach.
The script below allows you to rebuild indexes for all databases and all tables within a database. This could be further tweaked to handle only indexes that need maintenance as well as doing either index defrags or index rebuilds.
The script uses two cursors one for the databases and another cursor for the tables within the database. In addition, it uses the INFORMATION_SCHEMA.TABLES view to list all of the tables within a database.
1/5 1 2 3 4 5 下一页 尾页 |
