Reindex All Tables in a SQL Database

Last night, our database server experienced a dramatic slow down. We found out that the bottleneck is during SELECT statements on one of the main tables. Our initial reaction is to purge data from this tables but doing it requires approval from the system owner. While waiting for the approval, we decided to perform reindexing on all tables. Here is a small script that we used which you can run using Query Analyzer.

CREATE PROCEDURE spReindexAllTables AS
    SET NOCOUNT ON
    DECLARE @TblName char(100)
    DECLARE IdxCursor CURSOR FOR SELECT name FROM sysobjects WHERE type='U'
    OPEN IdxCursor
    FETCH NEXT FROM IdxCursor INTO @TblName
    WHILE @@FETCH_STATUS=0 BEGIN
        DBCC DBREINDEX(@TblName)
        FETCH NEXT FROM IdxCursor INTO @TblName
    END
    CLOSE IdxCursor
    DEALLOCATE IdxCursor 
GO

The procedure above select all table names from the sysobjects table and loop through each table then executes the reindex command on it.


For More SQL Tips & Tricks, subscribe now.