Script to Rebuild Index on all Tables

Hi,

Today let's discuss how we can rebuild all indexes on a database.

Here is the script that will generate Rebuild Index statement for all tables & execute it.

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 
DECLARE @minId INT
DECLARE @maxId INT

SET @fillfactor = 90 

CREATE TABLE #tmp
(ID INT NOT NULL IDENTITY, Tabname VARCHAR(1000))


INSERT INTO #tmp(Tabname)
SELECT table_catalog + '.' + table_schema + '.[' + table_name + ']' as tableName FROM  INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE'

SET @minId=1
SELECT @maxId=COUNT(1) FROM #tmp

WHILE @minId <= @maxId
BEGIN

SELECT @Table=tabname FROM #tmp WHERE id = @minId


SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'  

PRINT @cmd
EXEC (@cmd)  

SET @minId = @minId + 1
END  


DROP TABLE #tmp

Let me know if you have any issue while executing the script.

Regards,

Happy SQL Coding