A script for rebuilding all table indexes in SQL Server

le

For a DBAs maintaining multiple databases, monitoring the disk fragmentation and rebuilding the indexes for individual database is quite time consuming task. 

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

Here is a script that will rebuild all the indexes for all the tables in your SQL Server database. This script can be used in a maintenance plan and can be executed in a scheduled job.


USE your database name here

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

GO

EXEC sp_updatestats