2008 Index Rebuild using DMV
Previous Version (http://www.sqlservercentral.com/scripts/SQL+Server+2005/61278/)
Iterates through sys.tables; performs an online index rebuild on indexes where the logical fragmentation is greater than or equal to the @LogicalFragmentationPercent parameter. The procedure can perform offline rebuild on indexes containing text, ntext, image, xml, varchar(max), varbinary(max) and nvarchar(max) or an offline rebuild by partition. This procedure is intended to reside in the msdb database and will conditionally create and populate a table called dbo.index_maintenance_history, configured via the @MaintainHistory parameter. This procedure can also produce and HTML report via the @EmailNotification paramter.
To utilize this with RedGate's SQL Script Manager, please save T-SQL above as usp_DBA_index_maintenance_SSC.sql along with the *.rgtool code, preferably in the C:\ProgramData\Red Gate\SQL Scripts Manager directory.
In the past several readers have posted problems w/ downloading the T-SQL from the WYSWYG editor. Should you encounter any of these types of issues, I've made the scripts available via my SkyDrive.
create procedure usp_DBA_index_maintenance @DBName sysname,@Tables nvarchar(max) = NULL,@MaintainHistory bit = 0,@OutputResults bit = 1,@RebuildByPartition bit = 0,@IncludeOffline bit = 0,@LogicalFragmentationPercent int = 30,@MinPages int = 1000,@MaxDop int = 0,@FillFactor int = 80,@EmailNotification bit = 0,@EmailRecipients varchar(max) = '',@EmailSubject varchar(max) = 'Index Maintenance Report',@Debug bit = 0 as /* Name: usp_DBA_index_maintenance Author: Tommy Bollhofer tbollhofer2@gmail.com http://www.linkedin.com/in/tommybollhoferDescription: Iterates through sys.tables; performs an online index rebuild on indexes where the logical fragmentation is greater than or equal to the @LogicalFragmentationPercent parameter. The procedure can perform offline rebuild on indexes containing text, ntext, image, xml, varchar(max), varbinary(max) and nvarchar(max) or an offline rebuild by partition. This procedure is intended to reside in the msdb database and will conditionally create and populate a table called dbo.index_maintenance_history, configured via the @MaintainHistory parameter. This procedure can also produce and HTML report via the @EmailNotification paramter. Usage: exec dbo.usp_DBA_index_maintenance @DBName = 'AdventureWorks', @Tables = 'SomeTableName1,SomeTableName2', -- Optional @MaintainHistory = 1, @OutputResults bit = 1, @RebuildByPartition = 0, @IncludeOffline = 0, @LogicalFragmentationPercent = 30, @MinPages = 1000, @MaxDop = 0, @EmailNotification = 1, @EmailRecipients = 'DBA@gmail.com', @EmailSubject = 'Index Maintenance Report - AdventureWorks', @Debug = 0 Last Modified: 02/24/2012 */ set nocount onif @RebuildByPartition = 1 and @IncludeOffline = 1begin set @IncludeOffline = 0enddeclare @table_guid varchar(36)set @table_guid = replace(newid(),'-','_')exec ('if exists (select name from tempdb..sysobjects where name = ''##clustered_indexes_with_offline_data_types_' + @table_guid + ''')begin drop table ##clustered_indexes_with_offline_data_types_' + @table_guid + 'end ')exec ('if exists (select name from tempdb..sysobjects where name = ''##nonclustered_indexes_with_offline_data_types_' + @table_guid + ''')begin drop table ##nonclustered_indexes_with_offline_data_types_' + @table_guid + 'end ')create table #fragreport( [rid] int identity (1,1), [database_id] int, [database_name] sysname,[schema_id] int,[schema_name] sysname,[table_id] int,[table_name] sysname,[index_id] int,[index_name] sysname,[partition_scheme] sysname NULL,[partition_number] int,[pre_fragmentation_in_percent] float,[post_fragmentation_in_percent] float,[page_count] int,[ddl_operation] int,[ddl_text] char(100),[insert_date] datetime default getdate()) create table #index_rebuild_by_partition( [rid] int identity (1,1),[schema_id] int, [schema_name] sysname, [table_id] int,[table_name] sysname, [index_name] sysname,[index_id] int,[partition_number] int) create table #index_rebuild_offline( [rid] int identity (1,1),[schema_id] int, [schema_name] sysname, [table_id] int,[table_name] sysname, [index_name] sysname,[index_id] int,[partition_number] int) create table #index_rebuild_online( [rid] int identity (1,1), [schema_id] int, [schema_name] sysname, [table_id] int,[table_name] sysname, [index_name] sysname,[index_id] int,[partition_number] int) exec ('create table ##clustered_indexes_with_offline_data_types_' + @table_guid + '([table_id] int,[index_name] sysname,[index_id] int,[column_name] sysname)')exec ('create table ##nonclustered_indexes_with_offline_data_types_' + @table_guid + '([table_id] int,[index_name] sysname,[index_id] int,[index_column_id] int,[column_name] sysname,[is_included_column] bit,[column_position] int)')if @Debug = 1begin print 'TEMPORARY TABLES HAVE BEEN CREATED. GLOBAL TEMPORARY TABLE GUID IS ' + @table_guid + '.'end if @Tables IS NULL or @Tables = ''begin insert into #fragreport ( [database_id], [database_name], [schema_id], [schema_name], [table_id], [table_name], [index_id], [index_name], [partition_scheme], [partition_number] ) exec (' select distinct db_id( ''' + @DBName + ''' ) as [database_id], ''' + @DBName + ''' as [database_name], s.schema_id as [schema_id], s.[name] as [schema_name], t.[object_id] as [table_id], t.[name] as [table_name], i.[index_id] as [index_id], i.[name] as [index_name], ps.name as [partition_scheme], p.[partition_number] as [partition_number] from [' + @DBName + '].sys.tables t inner join [' + @DBName + '].sys.indexes i on t.[object_id] = i.[object_id]inner join [' + @DBName + '].sys.schemas s on t.[schema_id] = s.[schema_id]left join [' + @DBName + '].sys.partitions p on t.[object_id] = p.[object_id] and i.[index_id] = p.[index_id]left join [' + @DBName + '].sys.partition_schemes ps on i.data_space_id = ps.data_space_idleft join [' + @DBName + '].sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id where i.[index_id] <> 0 ') exec (' insert into ##nonclustered_indexes_with_offline_data_types_' + @table_guid + ' ( [table_id], [index_name], [index_id], [index_column_id], [column_name], [is_included_column], [column_position] ) select t.object_id as table_id, i.name as index_name, i.index_id, ic.index_column_id, c.name as column_name, ic.is_included_column, row_number() over (partition by ic.is_included_column order by ic.index_column_id) as column_position from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.indexes i on t.object_id = i.object_idinner join [' + @DBName + '].sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_idinner join [' + @DBName + '].sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id where c.system_type_id in (34,35,99,241) and i.type = 2 union select t.object_id as table_id, i.name as index_name, i.index_id, ic.index_column_id, c.name as column_name, ic.is_included_column, row_number() over (partition by ic.is_included_column order by ic.index_column_id) as column_position from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.indexes i on t.object_id = i.object_idinner join [' + @DBName + '].sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_idinner join [' + @DBName + '].sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id where c.system_type_id in (136,231,165,167) and c.max_length = -1 and i.type = 2') exec (' insert into ##clustered_indexes_with_offline_data_types_' + @table_guid + ' ( [table_id], [index_name], [index_id], [column_name] ) select t.object_id as table_id, i.name as index_name, i.index_id, c.name as column_name from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.schemas s on t.[schema_id] = s.[schema_id]inner join [' + @DBName + '].sys.indexes i on t.object_id = i.object_id inner join [' + @DBName + '].sys.columns c on i.object_id = c.object_id where c.system_type_id in (34,35,99,241) and i.type = 1 union select t.object_id as table_id, i.name as index_name, i.index_id, c.name as column_name from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.schemas s on t.[schema_id] = s.[schema_id]inner join [' + @DBName + '].sys.indexes i on t.object_id = i.object_id inner join [' + @DBName + '].sys.columns c on i.object_id = c.object_id where c.system_type_id in (136,231,165,167) and c.max_length = -1 and i.type = 1 ') if @Debug = 1begin select * from #fragreport exec ('select * from ##nonclustered_indexes_with_offline_data_types_' + @table_guid) exec ('select * from ##clustered_indexes_with_offline_data_types_' + @table_guid)end end if @Tables IS NOT NULLbegin if @Debug = 1 begin print 'TABLES SPECIFIED: ' + @Tables end select @Tables = replace(@Tables,',',''',''') insert into #fragreport ( [database_id], [database_name], [schema_id], [schema_name], [table_id], [table_name], [index_id], [index_name], [partition_scheme], [partition_number] ) exec (' select distinct db_id( ''' + @DBName + ''' ) as [database_id], ''' + @DBName + ''' as [database_name], s.schema_id as [schema_id], s.[name] as [schema_name], t.[object_id] as [table_id], t.[name] as [table_name], i.[index_id] as [index_id], i.[name] as [index_name], ps.name as [partition_scheme], p.[partition_number] as [partition_number] from [' + @DBName + '].sys.tables t inner join [' + @DBName + '].sys.indexes i on t.[object_id] = i.[object_id]inner join [' + @DBName + '].sys.schemas s on t.[schema_id] = s.[schema_id]left join [' + @DBName + '].sys.partitions p on t.[object_id] = p.[object_id] and i.[index_id] = p.[index_id]left join [' + @DBName + '].sys.partition_schemes ps on i.data_space_id = ps.data_space_idleft join [' + @DBName + '].sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id where i.[index_id] <> 0 and t.[name] in (''' + @Tables + ''')') exec (' insert into ##nonclustered_indexes_with_offline_data_types_' + @table_guid + ' ( [table_id], [index_name], [index_id], [index_column_id], [column_name], [is_included_column], [column_position] ) select t.object_id as table_id, i.name as index_name, i.index_id, ic.index_column_id, c.name as column_name, ic.is_included_column, row_number() over (partition by ic.is_included_column order by ic.index_column_id) as column_position from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.indexes i on t.object_id = i.object_idinner join [' + @DBName + '].sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_idinner join [' + @DBName + '].sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id where c.system_type_id in (34,35,99,241) and i.type = 2 and t.[name] in (''' + @Tables + ''') union select t.object_id as table_id, i.name as index_name, i.index_id, ic.index_column_id, c.name as column_name, ic.is_included_column, row_number() over (partition by ic.is_included_column order by ic.index_column_id) as column_position from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.indexes i on t.object_id = i.object_idinner join [' + @DBName + '].sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_idinner join [' + @DBName + '].sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id where c.system_type_id in (136,231,165,167) and c.max_length = -1 and i.type = 2 and t.[name] in (''' + @Tables + ''')') exec (' insert into ##clustered_indexes_with_offline_data_types_' + @table_guid + ' ( [table_id], [index_name], [index_id], [column_name] ) select t.object_id as table_id, i.name as index_name, i.index_id, c.name as column_name from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.schemas s on t.[schema_id] = s.[schema_id]inner join [' + @DBName + '].sys.indexes i on t.object_id = i.object_id inner join [' + @DBName + '].sys.columns c on i.object_id = c.object_id where c.system_type_id in (34,35,99,241) and i.type = 1 and t.[name] in (''' + @Tables + ''') union select t.object_id as table_id, i.name as index_name, i.index_id, c.name as column_name from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.schemas s on t.[schema_id] = s.[schema_id]inner join [' + @DBName + '].sys.indexes i on t.object_id = i.object_id inner join [' + @DBName + '].sys.columns c on i.object_id = c.object_id where c.system_type_id in (136,231,165,167) and c.max_length = -1 and i.type = 1 and t.[name] in (''' + @Tables + ''')') if @Debug = 1begin select * from #fragreport exec ('select * from ##nonclustered_indexes_with_offline_data_types_' + @table_guid) exec ('select * from ##clustered_indexes_with_offline_data_types_' + @table_guid)end end if @Debug = 1begin print 'OBJECT INFORMATION HAS BEEN COLLECTED.'end declare @numtables int, @number_of_indexes int, @count_pre_index_statistics int, @count_post_index_statistics int, @number_of_index_rebuild_by_partition int, @number_of_index_rebuild_offline int, @number_of_index_rebuild_online int, @table_count int, @index_count int, @recount int, @database_id int, @schema_id int, @schema_name sysname, @table_id int, @table_name sysname, @index_id int, @index_name sysname, @partition_number int, @output_value int, @sql nvarchar(max) -- gather pre-fragmentation statistics update #fragreport set [pre_fragmentation_in_percent] = d.[avg_fragmentation_in_percent], [page_count] = d.[page_count] from #fragreport s cross apply (select [database_id], [object_id], [index_id], [partition_number], [index_type_desc], [alloc_unit_type_desc], [index_depth], [index_level], [avg_fragmentation_in_percent], [fragment_count], [avg_fragment_size_in_pages], [page_count], [avg_page_space_used_in_percent], [record_count], [ghost_record_count], [version_ghost_record_count], [min_record_size_in_bytes], [max_record_size_in_bytes], [avg_record_size_in_bytes], [forwarded_record_count] from sys.dm_db_index_physical_stats (s.database_id, s.table_id, s.index_id, s.partition_number, NULL)) d if @Debug = 1begin print 'PRE-FRAGMENTATION STATISTICAL INFORMATION HAS BEEN COLLECTED.'end -- indexes by partition insert into #index_rebuild_by_partition ( [schema_id], [schema_name], [table_id], [table_name], [index_id], [index_name], [partition_number] ) exec (' select distinct f.[schema_id], f.[schema_name], f.[table_id], f.[table_name], f.[index_id], f.[index_name], f.[partition_number] from #fragreport f where (f.[page_count] > ' + @MinPages + ') and (f.[pre_fragmentation_in_percent] > ' + @LogicalFragmentationPercent + ') ') -- indexes by offline data types insert into #index_rebuild_offline ( [schema_id], [schema_name], [table_id], [table_name], [index_id], [index_name], [partition_number] ) exec (' select distinct f.[schema_id], f.[schema_name], f.[table_id], f.[table_name], f.[index_id], f.[index_name], f.[partition_number] from #fragreport finner join ##clustered_indexes_with_offline_data_types_' + @table_guid + ' o on f.[table_id] = o.[table_id]inner join ##nonclustered_indexes_with_offline_data_types_' + @table_guid + ' o2 on f.[table_id] = o2.[table_id] where (f.[page_count] > ' + @MinPages + ') and (f.[pre_fragmentation_in_percent] > ' + @LogicalFragmentationPercent + ') ') -- indexes by online data types insert into #index_rebuild_online ( [schema_id], [schema_name], [table_id], [table_name], [index_id], [index_name], [partition_number] ) exec (' select distinct f.[schema_id], f.[schema_name], f.[table_id], f.[table_name], f.[index_id], f.[index_name], f.[partition_number] from #fragreport fleft outer join ##clustered_indexes_with_offline_data_types_' + @table_guid + ' o on f.[table_id] = o.[table_id]left outer join ##nonclustered_indexes_with_offline_data_types_' + @table_guid + ' o2 on f.[table_id] = o2.[table_id] where (f.[page_count] > ' + @MinPages + ') and (f.[pre_fragmentation_in_percent] > ' + @LogicalFragmentationPercent + ') and coalesce(o.[table_id],o.[table_id]) is null and coalesce(o2.[table_id],o2.[table_id]) is null ') exec (' update #fragreport set [ddl_operation] = 0, [ddl_text] = ''REBUILD MUST BE PERFORMED OFFLINE'' from #fragreport s inner join ##clustered_indexes_with_offline_data_types_' + @table_guid + ' o on s.[table_id] = o.[table_id]inner join ##nonclustered_indexes_with_offline_data_types_' + @table_guid + ' o2 on s.[table_id] = o2.[table_id] where (s.[page_count] > ' + @MinPages + ') and (s.[pre_fragmentation_in_percent] > ' + @LogicalFragmentationPercent + ') ') update #fragreport set [ddl_operation] = 4, [ddl_text] = 'INDEX CONTAINS < ' + convert(char(4),@MinPages) + ' PAGES' from #fragreport where [page_count] <= @MinPages if @Debug = 1begin print 'INDEXES HAVE BEEN SPIT INTO CATEGORIES; PARTITIONED, OFFLINE, AND ONLINE.'end -- rebuild indexes by partition; implies offlineif @RebuildByPartition = 1 and @IncludeOffline = 0begin if (select count(*) from #index_rebuild_by_partition) > 0 begin select @number_of_index_rebuild_by_partition = count(*) from #index_rebuild_by_partition set @recount = 1 while @recount <= @number_of_index_rebuild_by_partition begin select @schema_id = [schema_id], @schema_name = [schema_name], @table_id = [table_id], @table_name = [table_name], @index_id = [index_id], @index_name = [index_name], @partition_number = [partition_number] from #index_rebuild_by_partition where rid = @recount exec ('use [' + @dbname + '] alter index ' + @index_name + ' on ' + @schema_name + '.' + @table_name + ' rebuild partition = ' + @partition_number + ' with(maxdop = ' + @MaxDop + ')') update #fragreport set [ddl_operation] = 1, [ddl_text] = 'INDEX WAS REBUILT OFFLINE BY PARTITION' where [schema_id] = @schema_id and [table_id] = @table_id and [index_id] = @index_id and [partition_number] = @partition_number set @recount = @recount + 1 end end if @Debug = 1 begin print 'INDEX REBUILD OPERATIONS BY PARTITION HAVE BEEN COMPLETED.' end end -- rebuild online indexes; include offline indexesif @RebuildByPartition = 0 and @IncludeOffline = 1beginif (select count(*) from #index_rebuild_offline) > 0 begin select @number_of_index_rebuild_offline = count(*) from #index_rebuild_offline set @recount = 1 while @recount <= @number_of_index_rebuild_offline begin select @schema_id = [schema_id], @schema_name = [schema_name], @table_id = [table_id], @table_name = [table_name], @index_id = [index_id], @index_name = [index_name], @partition_number = [partition_number] from #index_rebuild_offline where rid = @recount exec ( 'use [' + @dbname + '] alter index ' + @index_name + ' on ' + @schema_name + '.' + @table_name + ' rebuild with(online = off, fillfactor = ' + @FillFactor + ', maxdop = ' + @MaxDop + ')' ) update #fragreport set [ddl_operation] = 2, [ddl_text] = 'INDEX WAS REBUILT OFFLINE' where [schema_id] = @schema_id and [table_id] = @table_id and [index_id] = @index_id and [partition_number] = @partition_number set @recount = @recount + 1 end end if (select count(*) from #index_rebuild_online) > 0 begin select @number_of_index_rebuild_online = count(*) from #index_rebuild_online set @recount = 1 while @recount <= @number_of_index_rebuild_online begin select @schema_id = [schema_id], @schema_name = [schema_name], @table_id = [table_id], @table_name = [table_name], @index_id = [index_id], @index_name = [index_name], @partition_number = [partition_number] from #index_rebuild_online where rid = @recount exec ( 'use [' + @dbname + '] alter index ' + @index_name + ' on ' + @schema_name + '.' + @table_name + ' rebuild with(online = on, fillfactor = ' + @FillFactor + ', maxdop = ' + @MaxDop + ')' ) update #fragreport set [ddl_operation] = 3, [ddl_text] = 'INDEX WAS REBUILT ONLINE' where [schema_id] = @schema_id and [table_id] = @table_id and [index_id] = @index_id and [partition_number] = @partition_number set @recount = @recount + 1 end end if @Debug = 1 begin print 'OFFLINE/ONLINE INDEX REBUILD OPERATIONS HAVE BEEN COMPLETED.' end end -- rebuild online indexesif @RebuildByPartition = 0 and @IncludeOffline = 0beginif (select count(*) from #index_rebuild_online) > 0 begin select @number_of_index_rebuild_online = count(*) from #index_rebuild_online set @recount = 1 while @recount <= @number_of_index_rebuild_online begin select @schema_id = [schema_id], @schema_name = [schema_name], @table_id = [table_id], @table_name = [table_name], @index_id = [index_id], @index_name = [index_name], @partition_number = [partition_number] from #index_rebuild_online where rid = @recount exec ( 'use [' + @dbname + '] alter index ' + @index_name + ' on ' + @schema_name + '.' + @table_name + ' rebuild with(online = on, fillfactor = ' + @FillFactor + ', maxdop = ' + @MaxDop + ')' ) update #fragreport set [ddl_operation] = 3, [ddl_text] = 'INDEX WAS REBUILT ONLINE' where [schema_id] = @schema_id and [table_id] = @table_id and [index_id] = @index_id and [partition_number] = @partition_number set @recount = @recount + 1 end end if @Debug = 1 begin print 'ONLINE INDEX REBUILD OPERATIONS HAVE BEEN COMPLETED.' end end -- gather post-fragmentation statistics update #fragreport set [post_fragmentation_in_percent] = d.[avg_fragmentation_in_percent] from #fragreport s cross apply (select [database_id], [object_id], [index_id], [partition_number], [index_type_desc], [alloc_unit_type_desc], [index_depth], [index_level], [avg_fragmentation_in_percent], [fragment_count], [avg_fragment_size_in_pages], [page_count], [avg_page_space_used_in_percent], [record_count], [ghost_record_count], [version_ghost_record_count], [min_record_size_in_bytes], [max_record_size_in_bytes], [avg_record_size_in_bytes], [forwarded_record_count] from sys.dm_db_index_physical_stats (s.database_id, s.table_id, s.index_id, s.partition_number, NULL)) d if @Debug = 1begin print 'POST-FRAGMENTATION STATISTICAL INFORMATION HAS BEEN COLLECTED.'end if @MaintainHistory = 1begin if not exists (select * from msdb.sys.objects where name = 'index_maintenance_history') begin create table msdb.dbo.index_maintenance_history ( [rid] int identity (1,1), [database_name] sysname, [schema_name] sysname, [table_name] sysname, [index_name] sysname, [partition_scheme] sysname NULL, [partition_number] int, [pre_fragmentation_in_percent] float, [post_fragmentation_in_percent] float, [page_count] int, [ddl_operation] int, [ddl_text] char(100), [insert_date] datetime default getdate() ) end insert into msdb.dbo.index_maintenance_history ( [database_name], [schema_name], [table_name], [index_name], [partition_scheme], [partition_number], [pre_fragmentation_in_percent], [post_fragmentation_in_percent], [page_count], [ddl_operation], [ddl_text], [insert_date] ) select [database_name], [schema_name], [table_name], [index_name], isnull([partition_scheme],'Default') as [partition_scheme], isnull([partition_number],1) as [partition_number], isnull([pre_fragmentation_in_percent],0) as [pre_fragmentation_in_percent], --isnull(isnull([post_fragmentation_in_percent],[pre_fragmentation_in_percent]),0) as [post_fragmentation_in_percent], isnull([post_fragmentation_in_percent],0) as [post_fragmentation_in_percent], isnull([page_count],0) as [page_count], isnull([ddl_operation],9) as [ddl_operation], isnull([ddl_text],'NO ACTION NECESSARY') as [ddl_text], [insert_date] from #fragreport if @Debug = 1 begin print 'HISTORICAL INFORMATION HAS BEEN COLLECTED.' end end if @EmailNotification = 1begin declare @tableHTML nvarchar(max); set @tableHTML = N'<STYLE TYPE="text/css">TD{font-family: calibri; font-size: 9pt;}</STYLE>' + N'<b><font face="calibri" size="2">Index(s) targeted for maintenance operations have logical fragmentation > than ' + convert(char(2),@LogicalFragmentationPercent) + ' percent. Indexes with < ' + convert(char(4),@MinPages) + ' pages are ignored by default.</font></b><br><br>' + N'<table border="1" cellpadding="2" cellspacing="2" border="1">' + N'<tr><th><font face="calibri" size="2">Database Name</font></th>' + N'<th><font face="calibri" size="2">Table Name</font></th>' + N'<th><font face="calibri" size="2">Index Name</font></th>' + N'<th><font face="calibri" size="2">Partition Scheme</font></th>' + N'<th><font face="calibri" size="2">Partition Number</font></th>' + N'<th><font face="calibri" size="2">Operation</font></th>' + N'<th><font face="calibri" size="2">Pre-Logical Fragmentation</font></th>' + N'<th><font face="calibri" size="2">Post-Logical Fragmentation</font></th>' + N'<th><font face="calibri" size="2">Index Page Count</font></th>' + cast ( ( select td = [database_name], '', td = [schema_name] + '.' + [table_name], '', td = [index_name], '', td = isnull([partition_scheme],'Default'), '', td = isnull([partition_number],1), '', case when [ddl_operation] = 0 then '<td bgcolor="#FFFF00">REBUILD MUST BE PERFORMED OFFLINE</td>' when [ddl_operation] = 1 then '<td bgcolor="#FF0000">INDEX WAS REBUILT OFFLINE BY PARTITION</td>' when [ddl_operation] = 2 then '<td bgcolor="#FF0000">INDEX WAS REBUILT OFFLINE</td>' when [ddl_operation] = 3 then '<td bgcolor="#FF0000">INDEX WAS REBUILT ONLINE</td>' when [ddl_operation] = 4 then '<td bgcolor="#00FF00">INDEX CONTAINS < ' + convert(char(4),@MinPages) + ' PAGES</td>' else '<td bgcolor="#00FF00">NO ACTION NECESSARY</td>' end,'', td = convert(char,convert(int,isnull([pre_fragmentation_in_percent],0))) + '%', '', --td = convert(char,convert(int,isnull(isnull([post_fragmentation_in_percent],[pre_fragmentation_in_percent]),0))) + '%', '', td = convert(char,convert(int,isnull([post_fragmentation_in_percent],0))) + '%', '', td = convert(char,isnull([page_count],0)), '' from #fragreport order by [table_name] for xml path('tr'), type ) as nvarchar(max) ) + N'</table>' ; --Format the HTML set @tableHTML = REPLACE( @tableHTML, '<', '<' ); set @tableHTML = REPLACE( @tableHTML, '>', '>' ); set @tableHTML = REPLACE( @tableHTML, '&', '&' ); exec msdb.dbo.sp_send_dbmail @recipients=@EmailRecipients,@subject = @EmailSubject,@body = @tableHTML,@body_format = 'HTML'; if @Debug = 1 begin print 'E-MAIL NOTIFICATION HAS BEEN SENT.' end end if @OutputResults = 1begin select [database_name], [schema_name], [table_name], [index_name], [partition_scheme], [partition_number], [pre_fragmentation_in_percent], [post_fragmentation_in_percent], [page_count], [ddl_text] from #fragreport end-- housekeeping drop table #fragreport drop table #index_rebuild_offline drop table #index_rebuild_onlinedrop table #index_rebuild_by_partitionexec ('if exists (select name from tempdb..sysobjects where name = ''##clustered_indexes_with_offline_data_types_' + @table_guid + ''')begin drop table ##clustered_indexes_with_offline_data_types_' + @table_guid + 'end ')exec ('if exists (select name from tempdb..sysobjects where name = ''##nonclustered_indexes_with_offline_data_types_' + @table_guid + ''')begin drop table ##nonclustered_indexes_with_offline_data_types_' + @table_guid + 'end ') 
