Alert Index fragmentation report

DECLARE @Document NVARCHAR(MAX) , @DocumentHead NVARCHAR(MAX) , @DocumentTail NVARCHAR(MAX) , @TableHead NVARCHAR(MAX) , @TableBody NVARCHAR(MAX) , @TableTail NVARCHAR(MAX) , @page_count_minimum SMALLINT , @fragmentation_minimum INT; SET @page_count_minimum =1000; SET @fragmentation_minimum = 30; SET @DocumentHead = N'' + N'' + N'' + N'' + N'

' + N'Below are the Indexes in Workbench2Prod Database with Average Fragmentation above 30 Percent' + N'

'; SET @DocumentTail = ''; SET @TableHead = N'' + N'' + N' ' + N' ' + N' ' + N' ' + N''; SET @TableTail = '

TableName	IndexName	AverageFragmentationPercent	PageCount

'; SELECT @TableBody = ( SELECT ROW_NUMBER() OVER ( ORDER BY avg_fragmentation_in_percent DESC ) % 2 AS [TRRow] , sys.objects.name AS [TD] , sys.indexes.name AS [TD] , CONVERT(VARCHAR(10), avg_fragmentation_in_percent, 121) AS [TD] , page_count AS [TD] FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') INNER JOIN sys.objects ON sys.objects.object_id = sys.dm_db_index_physical_stats.object_id INNER JOIN sys.indexes ON sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id WHERE avg_fragmentation_in_percent > @fragmentation_minimum AND objects.is_ms_shipped = 0 AND sys.dm_db_index_physical_stats.index_id > 0 and sys.indexes.name<>'NIX_AccountTicketNote_AccountTicketID_TicketStateID'--Excluded This particular index Condition on may5th 2016 AND page_count > @page_count_minimum ORDER BY avg_fragmentation_in_percent DESC FOR XML RAW('tr') , ELEMENTS ); IF @TableBody IS NULL BEGIN RETURN; END; ELSE BEGIN SET @TableBody = REPLACE(@TableBody, '_x0020_', SPACE(1)); SET @TableBody = REPLACE(@TableBody, '_x003D_', '='); SET @TableBody = REPLACE(@TableBody, '1',''); SET @TableBody = REPLACE(@TableBody, '0', ''); SELECT @Document = @DocumentHead + @TableHead + @TableBody+ @TableTail + @DocumentTail; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default', @recipients ='abc@abc', @subject = 'Average Index Fragmentation with above 30 Percent', @body = @Document, @body_format = 'HTML'; END END