Rebuild all the indexes of a table & Indexed views in a Database

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table/view are dropped and rebuilt in a single transaction. FOREIGN KEY constraints do not have to be dropped in advance.

In general, rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

To REBUILD all the indexes, the following syntax is sufficient –

ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR = Any number between 0 to 100);

Before attempting to REBUILD, we should first find the level of fragmentation using the below tsql –

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('tablename'), NULL, NULL , 'DETAILED')

GO

Microsoft Recommends

REBUILD IF > 30% framgmented

REORGANIZE IF >5 % and < 30% framgmented

I have prepared the following TSQL to rebuild all the indexes on the all the tables and all the indexed views of the database in use.

USE DatabaseName

GO

DECLARE @tsql NVARCHAR(MAX) 

DECLARE @fillfactor INT

SET @fillfactor = 90

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