I had the need to reindex all the tables in a SQL Server 2005 database this week and found some very good scripts that loop through the list of user tables using a cursor and reindex each table to improve performance and reduce the size of the data file. In addition I used the dbcc command to shrink the database after the reindexing had completed. Below is the code used.

USE [dbname]

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
    DBCC DBREINDEX(@TableName,' ',90)
    FETCH NEXT FROM TableCursor INTO @TableName
    END

CLOSE TableCursor

DEALLOCATE TableCursor

EXEC sp_updatestats

DBCC SHRINKDATABASE ([dbname], 10);
GO