Introduction
In my last post, I had explained what could be the best value of Fill Factor for the indexes in SQL Server and had promised to show a handy way to ReBuild all the indexes including the ones created on the Indexed Views.
Implementation
USE DBNameGODECLARE @tsql NVARCHAR(MAX)
DECLARE @fillfactor INT
SET @fillfactor = 70
SELECT @tsql = STUFF(( SELECT DISTINCT
';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
FROM sysobjects o
INNER JOIN sysindexes i
ON o.id = i.id WHERE o.xtype IN ('U','V')
AND i.name IS NOT NULL
FOR XML PATH('')), 1,1,'')
--PRINT @tsql EXEC sp_executesql @tsql Conclusion
This way, we can easily rebuild all the existing indexes on the Tables as well as the Indexed Views of the selected Database with an option to set the fillfactor as well. Hope, this script will prove to be handy.
