Rebuilding all table indexes

Index fragmentation can occur when any large data modification operations happen (INSERT, UPDATE, or DELETE statements) on tables.

For DBAs to maintain multiple databases 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 and reorders the index rows in contiguous pages.

So we can use DBCC DBREINDEX statement to rebuild all the indexes on all the tables in database.

DBCC DBREINDEX is efficient over dropping and recreating indexes.

This script can be used in a maintenance plan and can be executed in a scheduled job.

--Option 1

USE YourDBName

GO

EXEC sp_MSforeachtable@command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

GO

EXEC sp_updatestats

GO

--Option 2

USE YourDBName

GO

CREATE PROCEDURE ReIndexDB_N_UpdateStats

AS

DECLARE @Table VARCHAR(255)

DECLARE IXCursor

CURSOR FOR

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'base table'

OPEN IXCursor

FETCH NEXT

FROM IXCursor INTO @MyTable

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Reindexing Table:  ' + @MyTable

DBCC DBREINDEX(@Table, '', 80)

FETCH NEXT

FROM IXCursor INTO @Table

END

CLOSE IXCursor

DEALLOCATE IXCursor

EXEC sp_updatestats

GO

Execution of Stored Procedure sp_updatestats at the end of the Indexes process ensures updating stats of the database.

Click Here for more details