Rebuild all MSSQL tables' indexes

rebuild-all-indexes.sql

/* Original source: https://stackoverflow.com/a/74454347/399845 */
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

DECLARE @TableName varchar(255);
DECLARE @IndexName varchar(255);
DECLARE @Fragmentation FLOAT;
DECLARE @IndexScript varchar(255);

DECLARE @MinimumPercentage FLOAT = 5.0, @MinimumPage INT = 10;

IF OBJECT_ID('TempDB..#IndexStatistic') IS NOT NULL
BEGIN 
	DROP TABLE #IndexStatistic
END
SELECT 
    '[' + dbschemas.[name] + '].[' + dbtables.[name] + ']' TableName, 
    dbindexes.[name] IndexName,
    indexstats.avg_fragmentation_in_percent Fragmentation,
    indexstats.page_count [pages]
INTO #IndexStatistic
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
	AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
	AND dbindexes.[name] IS NOT NULL
    AND indexstats.avg_fragmentation_in_percent >= @MinimumPercentage
    AND indexstats.page_count > @MinimumPage
ORDER BY 
    indexstats.page_count ASC,
    indexstats.avg_fragmentation_in_percent ASC;
SELECT * FROM #IndexStatistic;

DECLARE TableCursor CURSOR LOCAL FAST_FORWARD FOR SELECT TableName, IndexName, Fragmentation FROM #IndexStatistic;
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @IndexName, @Fragmentation
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @IndexScript = CASE
		WHEN @Fragmentation >= 30.0 THEN 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName + ' REBUILD'
		WHEN @Fragmentation >= 5.0 THEN 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName + ' REORGANIZE'
		ELSE NULL
	END
    IF (@IndexScript IS NOT NULL)
    BEGIN
        RAISERROR (@IndexScript, 10, 0) WITH NOWAIT
        WAITFOR DELAY '00:00:01';
        EXEC(@IndexScript); 
    END

    FETCH NEXT FROM TableCursor INTO @TableName, @IndexName, @Fragmentation;
END  
CLOSE TableCursor;
DEALLOCATE TableCursor;
DROP TABLE #IndexStatistic;