SQL Server 2005 – Rebuild all indexes in table

Indexes always helps to improve query performance in table, clustered indexes and non clustered indexes are applied on table to faster retrievable of data. Stats of indexes goes down whenever they are accessed by queries or DML (Data Manipulation language ) operations like insert, update and delete. To improve performance, One has to rebuild indexes.

How to Rebuild table index by script ?

Below script can be use for generating rebuild script for all table in the database, here we are taking fill factor as 80 for safer side. execute below script in master.

USE MASTER

DECLARE @dbname VARCHAR(255)
DECLARE @tblname VARCHAR(255)
DECLARE @query NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 80

DECLARE DbCursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN (‘master’,’msdb’,’tempdb’,’model’,’distribution’)
ORDER BY 1

OPEN DbCursor

FETCH NEXT FROM DbCursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

set @query = ‘DECLARE tblCursor CURSOR FOR
SELECT ”[”+ [table_catalog]+”].”+”[”+[table_schema]+”].”+”[”+[table_name]+”]”
as tableName FROM ‘ + @dbname+ ‘.INFORMATION_SCHEMA.TABLES
WHERE table_type = ”BASE TABLE”’

EXEC (@query)
OPEN tblCursor

FETCH NEXT FROM tblCursor INTO @tblname
WHILE @@FETCH_STATUS = 0
BEGIN

— Below ALTER INDEX statement will work against SQL 2005 and above database
SET @query = ‘ALTER INDEX ALL ON ‘ + @tblname + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’

EXEC (@query)

print @query

— Below Rebuild index statement are used in SQL Server 2000
— DBCC DBREINDEX(@tblname,’ ‘,@fillfactor)

FETCH NEXT FROM tblCursor INTO @tblname
END
CLOSE tblCursor
DEALLOCATE tblCursor

FETCH NEXT FROM DbCursor INTO @dbname
END

CLOSE DbCursor
DEALLOCATE DbCursor

After executing above script for index rebuild, run the below query to update the stats
sp_upadtestats

In Sql server 2008, Use below script to rebuild all index of table

alter index all on ‘tablename’
rebuild with (fillfactor = 80)

 

Leave a Reply

  

  

  


9 − 4 =