DBREINDEX

A SQL Server database will execute queries (reports) faster by re-indexing its tables.

DBCC DBREINDEX re-builds indices for a table. DBCC DBREINDEX can rebuild all the indices for a table in one statement (much easier than running several DROP INDEX and CREATE INDEX sql statements and also DBCC DBREINDEX provides the advantage of getting more optimizations than individual DROP INDEX and CREATE INDEX sql statements).

NOTE: DBCC DBREINDEX cannot be used for system tables.The following script can be used to re-build the indices of a database:

-- This script re-builds the indices of a SQL Server database.
-- Author: G. R. Roosta
-- License: Free To Use (No Restriction)

USE database_name;

GO

DECLARE @TableName varchar(255)

DECLARE fabriclake CURSOR FOR

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'base table'

OPEN fabriclake

FETCH NEXT FROM fabriclake INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC DBREINDEX(@TableName,' ',90)

FETCH NEXT FROM fabriclake INTO @TableName

END

CLOSE fabriclake

DEALLOCATE fabriclake