SQL Server script to rebuild all indexes for all tables and all databases

 

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. 

Because we need to change from database to database we also need to create dynamic SQL code for the queries.  For the DBCC DBREINDEX option we can just pass in the parameters, but for the ALTER INDEX statement we need to again build the query dynamically.

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255
DECLARE @cmd NVARCHAR(500
DECLARE @fillfactor INT

SET 
@fillfactor 90

DECLARE DatabaseCursor CURSOR FOR 
SELECT 
name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')  
ORDER BY 

OPEN DatabaseCursor 

FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS 
BEGIN 

   SET 
@cmd 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName  
                    FROM ' 
@Database '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''  

   
-- create table cursor 
   
EXEC (@cmd
   
OPEN TableCursor  

   
FETCH NEXT FROM TableCursor INTO @Table  
   
WHILE @@FETCH_STATUS 0  
   
BEGIN  

       
-- SQL 2000 command 
       --DBCC DBREINDEX(@Table,' ',@fillfactor)  
        
       -- SQL 2005 command 
       
SET @cmd 'ALTER INDEX ALL ON ' @Table ' REBUILD WITH (FILLFACTOR = ' CONVERT(VARCHAR(3),@fillfactor) + ')' 
       
EXEC (@cmd

       
FETCH NEXT FROM TableCursor INTO @Table  
   
END  

   CLOSE 
TableCursor  
   
DEALLOCATE TableCursor 

   
FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE 
DatabaseCursor  
DEALLOCATE DatabaseCursor 

The DBCC DBREINDEX command will work for both SQL 2000 and SQL 2005, but the new syntax that you should use for SQL Server 2005 is the ALTER INDEX command, so based on this you could do the following:

  • SQL 2000
    • uncomment this line
      • DBCC DBREINDEX(@Table,' ',@fillfactor)  
    • comment these lines
      • SET @cmd 'ALTER INDEX ALL ON ' @Table ' REBUILD WITH (FILLFACTOR = ' CONVERT(VARCHAR(3),@fillfactor) + ')' 
      • EXEC (@cmd)
  • SQL 2005
    • comment this line
      • DBCC DBREINDEX(@Table,' ',@fillfactor)  
    • uncomment these lines
      • SET @cmd 'ALTER INDEX ALL ON ' @Table ' REBUILD WITH (FILLFACTOR = ' CONVERT(VARCHAR(3),@fillfactor) + ')' 
      • EXEC (@cmd)

Also, I have excluded the system databases, so you can include these or also add other databases to exclude from you index maintenance routines.

Next Steps

  • This is a simple base script that could be modified into a stored procedure and also allow you to pass other parameters such as doing an index rebuild or an index defrag.
  • Make the index rebuild statements more robust with other options.
  • You could also modify this to read from a table that you create to identify which databases you want to run this against instead of using the sysdatabases table.
  • Take a look at these other index related tips
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值