HTML Format SQL Report via Email

Here is nice and easy way to generate a formatted html table that will send  a HTML output email using sp_send_dbmail in SQL 2005.

SET @tableHTML1 = N‘<font face=”Times New Roman, Arial” color=”Blue”><H1>Database Backup List.</H1></font>’ + N‘<font size=”4″ color=”maroon”><H3>Server: ENTER SERVER NAME HERE</H3></font>’ + N‘<table border=”1″>’ + N‘<tr><th>Database</th>’ + — Defined column Name N‘<th>Last Backup</th>’ +  — Defined column Name N‘<th>Comments</th>’ +     — Defined column Name CAST ( ( SELECT  “td/@align” = ‘left’, td = SUBSTRING(t1.name,1,40), ”,                  “td/@align” = ‘left’, td = CAST(t2.backup_start_date AS char(11)), ”,                  “td/@align” = ‘left’,                  “td/@bgcolor” = CASE WHEN t2.backup_start_date < DATEADD(dd,-7,getdate())                                       THEN ‘#FF3366’ END,                   td = CASE WHEN t2.backup_start_date > DATEADD(dd,-1,getdate())                                       THEN ‘Backup is current within a day’                                       WHEN t2.backup_start_date > DATEADD(dd,-7,getdate())                                       THEN ‘Backup is current within a week’                                       ELSE  ‘****CHECK BACKUP!!!****’                                       END, ”             FROM master.dbo.sysdatabases t1                     Left OUTER JOIN                   (SELECT t3.database_name, MAX(t3.backup_start_date) as ‘backup_start_date’                             FROM   msdb.dbo.backupset t3                                                        WHERE t3.type = ‘D’                         GROUP BY t3.database_name)t2  ON t1.name = t2.database_name                            WHERE t1.name Not In (‘Northwind’) — Filter what DB you want             ORDER BY    t1.name             FOR XML PATH(‘tr’), TYPE            ) AS NVARCHAR(MAX) ) +   N‘</table>’ ;   BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘Sql Profile Here’, — Verify Sql mail set up on sql server @recipients = ‘Enter Email Here’, @subject = ‘Database Backup Status: Thanks For Reading ‘, @body = @tableHTML1,  @Body_Format = ‘HTML’ END

How to add Color to the HTML Table…

This will add the color red to the HTML TD table cell Comments if backup date is greater than 7 days.

“td/@bgcolor” = CASE WHEN t2.backup_start_date < DATEADD(dd,-7,getdate())                      THEN ‘#FF3366’ END,