Automatically Reindex All Tables in a SQL Server Database

This script will automatically reindex all indexes the tables in a selected database. When DBCC DBREINDEX is used to rebuild indexes, bear in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by users.

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

Syntax:

 

DBCC DBREINDEX 
( 
    table_name 
    [ , index_name [ , fillfactor ] ]
)
    [ WITH NO_INFOMSGS ]