Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

SQL Table of Results in an HTML email
Published: Oct 03, 2014
To be honest I couldn’t think of a good title for this particular post so I’ve gone with the above. Basically what I’m referring to is that sometimes a simple text email isn’t enough and you want to include some output with it… therefore what you need is a table.

Creating a table in an email body is not the simplest of things and I’ve seen numerous people struggle to create one, ending up doing something incredibly complex with their output, or even trying external plugins.

Luckily it’s not that difficult if you use a little XML. To clarify at this point, I’m not very good with XML myself and therefore you don’t have to have an in depth knowledge to do this, in fact it’s really quite simple to manipulate.

In this example I’ll create a simple table and then put it into an email. Once you know the technique it’s easy to apply to all manner of alerts and reporting you may want to schedule. It does look VERY complicated at first glance, but once you understand it, it’s really not.

Let’s start with a simple table and a couple of lines of data:

if object_id('tempDB..myEmailTable') is not null drop table myEmailTable

create table myEmailTable
id tinyint identity(1, 1),
firstName varchar(15),
lastName varchar(20),
isAdult bit

insert into myEmailTable
values('Homer', 'Simpson', 1),
'Marge', 'Simpson', 1),
'Bart', 'Simpson', 0),
'Lisa', 'Simpson', 0),
'Maggie', 'Simpson', 0)

Now we have our data, let’s presume that we would like to receive an email each day which lets us know who the Adults are in our table. (I’ve no idea why this would be useful, but it’s just an example so we’ll go with it)

The way to achieve this would be as follows:

declare @emailSubject varchar(100),
@textTitle varchar(100),
@columnHeaders varchar(1000),
@tableHTML nvarchar(max)

select @emailSubject = 'My Test Email',
@textTitle = 'All adults in #myEmailTable'

select @columnHeaders = isnull(@columnHeaders + '</th><th>', '') + c.name
from sys.objects o
join sys.columns c
on o.object_id = c.object_id
where o.name = 'myEmailTable'
and c.name != 'id' -- Because I don't want this column in the output
order by c.column_id

set @tableHTML =
'<div><b>Database Disk Usage:</b></div><br>' +
'<table border="1" cellpadding="5"><font face="Calibri" size=2>' +
'<tr><th>' + @columnHeaders + '</th></tr>' +
select td = firstName, '',  -- Here we put the column names
td = lastName, '',  -- Here we put the column names
td = isAdult, ''    -- Here we put the column names
from myEmailTable
where isAdult = 1           -- Normal WHERE clause
order by id
for xml path('tr'), type
)) +
exec msdb.dbo.sp_send_dbmail
@profile_name = My SQL Mail Server',
@recipients = 'theBoredDBA@theBoredDBA.com',
@body = @tableHTML,
@subject = @emailSubject,
@body_format = 'HTML'

Hopefully you can see that this is quite simple to follow as it has clearly named variables and a couple of comments. Running this (obviously change your Mail Server name and recipient email address) should result in the following:

It’s really that simple. If you know any HTML then you can easily play with this a little and make it look a little more professional, but if not then look out for my next post as I’ll provide some formatting for you to tinker with.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.