DECLARE @SchemeName NVARCHAR(MAX)=N'';

DECLARE @TableName NVARCHAR(MAX)=N'';

DECLARE @IndexName NVARCHAR(MAX)=N'';

DECLARE @avg_fragmentation_in_percent FLOAT=0;

DECLARE @SQL NVARCHAR(MAX)=N'';

DECLARE cur_index CURSOR

LOCAL

FORWARD_ONLY

FAST_FORWARD

READ_ONLY

FOR

SELECT

    '['+s.name+']' AS SchemeName,

    '['+o.name+']' AS TableName,

    '['+i.name+']' AS IndexName,

    MAX(ps.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent

FROM sys.indexes i

INNER JOIN sys.objects o

    ON i.object_id = o.object_id

INNER JOIN sys.schemas s

    ON o.schema_id = s.schema_id

INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'DETAILED') AS ps

    ON ps.object_id = i.object_id

    AND ps.index_id = i.index_id

WHERE ps.avg_fragmentation_in_percent >= 10

AND i.type IN (1, 2)    --1: CLUSTERED, 2: NONCLUSTERED

AND o.type = N'U'        --U: USER_TABLE

AND ps.index_level = 0    --Index leaf-level

GROUP BY    s.name,

            o.name,

            i.name

ORDER BY avg_fragmentation_in_percent DESC;

OPEN cur_index;

FETCH NEXT FROM cur_index

INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent;

WHILE(@@FETCH_STATUS=0)

BEGIN

    IF (@avg_fragmentation_in_percent>60)

    BEGIN

        SELECT @SQL = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemeName + N'.' + @TableName

                        + N' REBUILD PARTITION=ALL WITH (FILLFACTOR = 95, ONLINE = OFF, DATA_COMPRESSION = PAGE );'

    END

    ELSE  --@avg_fragmentation_in_percent between 40 and 60

    BEGIN

        SELECT @SQL = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemeName + N'.' + @TableName

                        + N' REORGANIZE PARTITION=ALL;'

    END

    EXEC (@SQL)

    FETCH NEXT FROM cur_index

    INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent;

END

CLOSE cur_index;

DEALLOCATE cur_index;