SQL Server : Automatically Reindex All Tables in a SQL Server Database

Tags : reindex

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 your users.

Versions

SQL Server 2000 +

USE DatabaseName  

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

    

Report Script

Top Users