Script for rebuilding all table indexes in SQL Server

For DBAs to maintain multiple database and 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.
So for a DBA to rebuild all the indexes for all the database on the SQL Server with one script will help them in their database maintenance task.
Here is the 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.
SCRIPT:

USE DatabaseName –Enter the name of the database you want to reindex

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

Be the first to comment

Leave a Reply

Your email address will not be published.


*