28 March 2008 20:19 tonyrogerson

Send Table or View as embedded HTML <table> in an email – stored procedure

Ever wanted to take a table, permanent or temporary or a view and just email it straight from SQL where the query result sits in the email as a nicely formatted HTML table rather than that ugly text file attachment you get when using sp_send_dbmail? Ever hit that infinite deadlock I blogged about the other day if you use the sp_send_dbmail attachment feature? Well, this stored procedure is for you!

This is an expansion of an example in the sp_send_dbmail section in books online, all I’ve done is made it dynamically accept a table instead of you needing to hard code it.

The stored procedure is very basic and I’ll probably expand it considerably for a production environment (Monday Darren! - honest), you pass in the source database, schema and table or view name of the object you want to convert into a HTML table in your email.

Usage is simple; if you have a complex query just run it into a temporary table and pass that into the procedure.


select type, cnt = count(*)

into #toemail

from sys.objects

group by type


exec email_object_as_html @source_db    = 'tempdb',

                          @schema       = '',

                          @object_name  = '#toemail',

                          @order_clause = 'cnt desc, type',

                          @email = 'tonyrogerson@torver.net'


drop table #toemail

Sends an email as follows:-

Below is the table tempdb..#toemail converted from a query into HTML for your pleasure

type cnt
S 41
U 16
IT 3
SQ 3
AF 1
P 1
PC 1

Anyway have a play, usual provisos because I’m using dynamic SQL, I’ve not put the injection protection in this because I wanted to keep the proc in its simplest form for the blog. HTML HT

create proc email_object_as_html

    @source_db    sysname,       --  Where the @object_name is resident

    @schema       sysname,       --  Schema name eg.. dbo.

    @object_name  sysname,       --  Table or view to email

    @order_clause nvarchar(max), --  The order by clause eg. x, y, z

    @email        nvarchar(max)  --  Email recipient list




        Convert's the specified table or view into an html table and emails it.


        Tony Rogerson, SQL Server MVP

        28 March 2008



        You are free to use and modify this,

        just keep the above in place;

        I offer no warranties, you get this as is.




    declare @subject nvarchar(max),

               @body    nvarchar(max)


    --  Get columns for table headers..

    exec( '

    declare col_cur cursor for

        select name

        from ' + @source_db + '.sys.columns

        where object_id = object_id( ''' + @source_db + '.' + @schema + '.' + @object_name + ''')

        order by column_id

        ' )


    open col_cur


    declare @col_name sysname

    declare @col_list nvarchar(max)


    fetch next from col_cur into @col_name


    set @body = N'<table border=1 cellpadding=1 cellspacing=1><tr>'


    while @@fetch_status = 0


        set @body = cast( @body as nvarchar(max) )

                  + N'<th>' + @col_name + '</th>'


        set @col_list = coalesce( @col_list + ',', '' ) + ' td = ' + cast( @col_name as nvarchar(max) ) + ', '''''


        fetch next from col_cur into @col_name




    deallocate col_cur


    set @body = cast( @body as nvarchar(max) )

              + '</tr>'


    declare @query_result nvarchar(max)

    declare @nsql nvarchar(max)


    --  Form the query, use XML PATH to get the HTML

    set @nsql = '

        select @qr =

               cast( ( select ' + cast( @col_list as nvarchar(max) )+ '

                       from ' + @source_db + '.' + @schema + '.' + @object_name + '

                       order by ' + @order_clause + '

                       for xml path( ''tr'' ), type

                       ) as nvarchar(max) )'


    exec sp_executesql @nsql, N'@qr nvarchar(max) output', @query_result output


    set @body = cast( @body as nvarchar(max) )

              + @query_result


    --  Send notification

    set @subject = 'Your table as requested'


    set @body = @body + cast( '</table>' as nvarchar(max) )


    set @body = '<p>Below is the table ' + @source_db + '.' + @schema + '.' + @object_name

              + ' converted from a query into HTML for your pleasure</p>'

              + cast( @body as nvarchar(max) )


    EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'TORVER',

                                  @recipients = @email,

                                  @body = @body,

                                  @body_format = 'HTML',

                                  @subject = @subject



Filed under:


# Interesting Finds: March 29, 2008

29 March 2008 15:33 by Jason Haley

# Interesting Finds: March 29, 2008

02 April 2008 02:37 by Jason Haley

# Format query output into an HTML table - the easy way

24 October 2008 09:20 by Tony Rogerson's ramblings on SQL Server

Ok, I wrote a big stored procedure last time my entry on &quot; send table or view as embedded html &quot;;

# sp_send_dbmail with attachment issue | keyongtech

Pingback from  sp_send_dbmail with attachment issue | keyongtech

# Mailing myself an XML table. | keyongtech

Pingback from  Mailing myself an XML table. | keyongtech

# re: Send Table or View as embedded HTML <table> in an email – stored procedure

15 December 2010 19:06 by jj00

Just wanted to let you know someone is still finding this post useful.  

I used part of this code with tsqlunit to create more detailed unit test failure messages.  I didn't need the email part, so I just made a few minor modifications for my use.


# Transform any query result to Html | mk

16 December 2014 02:02 by Transform any query result to Html | mk

Pingback from  Transform any query result to Html | mk