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),
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),
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
where isAdult = 1 -- Normal WHERE clause
order by id
for xml path('tr'), type
@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.