Convert a SQL query result table to an HTML table for email

I am running a SQL query that returns a table of results. I want to send the table in an email using dbo.sp_send_dbMail.

Is there a straightforward way within SQL to turn a table into an HTML table? Currently, I’m manually constructing it using COALESCE and putting the results into a varchar that I use as the emailBody.

Is there a better way to do this?

Answer

Here is one way to do it from an article titled “Format query output into an HTML table – the easy way [archive]“. You would need to substitute the details of your own query for the ones in this example, which gets a list of tables and a row count.

declare @body varchar(max)

set @body = cast( (
select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )
from (
      select dbtable  = object_name( object_id ),
             entities = count( distinct name ),
             rows     = count( * )
      from sys.columns
      group by object_name( object_id )
      ) as d
for xml path( 'tr' ), type ) as varchar(max) )

set @body = '<table cellpadding="2" cellspacing="2" border="1">'
          + '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'
          + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' )
          + '</table>'

print @body

Once you have @body, you can then use whatever email mechanism you want.