Sending Query Results to Email in HTML Format

Sending T-SQL Query Results to email in HTML Format


begin
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>Status Report of Databases</H1>' +
N'<table border="1">' +
N'<B> ServerName :' + CAST ( (select @@SERVERNAME for xml PATH , Type) As Nvarchar(MAX)) +
N'<br><th>Name</th> <th>Status></th>' +
CAST ( ( SELECT td = name, '',
td = DATABASEPROPERTYEX(name, 'status'), ''
FROM sys.databases where database_id > 4
FOR XML PATH ('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@recipients='hello@gmail.com',
@subject = 'Databases Status',
@body = @tableHTML,
@body_format = 'HTML' ;

end