SET NOCOUNT ON
DROP TABLE IF EXISTS [#Fragmented_Indexes]
DECLARE
@SchemaName sysname,
@TableName sysname,
@IndexName sysname,
@AvgFragmentationInPercent INT,
@PageCount INT,
@Fragmentation INT,
@Stmt NVARCHAR(MAX);
SET @PageCount = 1000
SET @AvgFragmentationInPercent = 10
SELECT
[s].[name] AS [SchemaName], [t].[name] AS [TableName], [i].[name] AS [IndexName], [ps].[avg_fragmentation_in_percent] [Fragmentation]
INTO [#Fragmented_Indexes]
FROM [sys].[tables] [t]
INNER JOIN [sys].[schemas] [s] ON [s].[schema_id] = [t].[schema_id]
INNER JOIN [sys].[indexes] [i] ON [i].[object_id] = [t].[object_id]
CROSS APPLY [sys].dm_db_index_physical_stats(DB_ID(), [t].[object_id], [i].[index_id], NULL, 'SAMPLED') [ps]
WHERE [i].[index_id] > 0 AND [i].[is_disabled] = 0 AND [i].[is_hypothetical] = 0 AND [ps].[alloc_unit_type_desc] = 'IN_ROW_DATA' AND
[ps].[avg_fragmentation_in_percent] > @AvgFragmentationInPercent AND
[ps].[page_count] > @PageCount
RAISERROR('%i indexes to be rebuilded', 10, 1, @@ROWCOUNT) WITH NOWAIT
WHILE EXISTS (SELECT * FROM [#Fragmented_Indexes])
BEGIN
SELECT TOP(1)
@SchemaName = [SchemaName],
@TableName = [TableName],
@IndexName = [IndexName],
@Fragmentation = [Fragmentation]
FROM [#Fragmented_Indexes]
ORDER BY [SchemaName], [TableName]
SET @Stmt =
N'ALTER INDEX ' + QUOTENAME(@IndexName) +
N' ON ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) +
N'REBUILD;'
RAISERROR('%s.%s => %s => %i to %i', 10, 1, @SchemaName, @TableName, @IndexName, @Fragmentation, @AvgFragmentationInPercent) WITH NOWAIT
EXEC [sys].[sp_executesql] @Stmt;
DELETE FROM [#Fragmented_Indexes] WHERE [SchemaName] = @SchemaName AND [TableName] = @TableName AND [IndexName] = @IndexName
END
GO
