A new management function in Sql Server 2012 can extract metadata from arbitrary queries. Paired with dynamic sql this allows to transform (almost) any query into a nice looking html table. This is particularly interesting if you want to send query results with Sql Server Database Mail.
I recently had a customer requesting to receive query results periodically per e-mail. Not having Sql Server Reporting Services available there are two solution paths to this problem:
- Create an excel file from the query and send as attachment
- Output the query to html and include it in the E-Mail body
Option 1 (sending data as attachment) would involve rather complicated clr-integrated programming and server-side generation of excel files. This would only be appropriate for larger data volumes. Also the implementation would take considerably more time and effort than option 2 (send as html).
This article describes a full solution to option 2. You can download the code file from the link at the end.
Sample data and output
To demonstrate the solution I made up some sample data – fictitious statistics of authoring activities on an international content platform.
I wanted to have the tabular query output formatted nicely and some text included before and after the table.
So, let’s produce a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
IF OBJECT_ID(N'sampleData20141215', 'U') IS NOT NULL DROP TABLE sampleData20141215 GO CREATE TABLE sampleData20141215 ( subscriber NVARCHAR(50), subId INT, country VARCHAR(3), total INT, articles INT, discussions INT, edits INT ); GO INSERT sampleData20141215 (subscriber, subId, country, total, articles, discussions, edits) VALUES ('Carlos, Juan', 16146, 'ES', 610, 200, 200, 210), ('Perez, Roberto', 3319, 'ES', 120, 10, 90, 20), ('Jiménez, Gina', 14249, 'ES', 70, 12, 30, 28), ('Rodriguez, Samuel', 17017, 'ES', 400, 80, 300, 20), ('Navarro, Jacquelyn', 5558, 'ES', 210, 90, 45, 75), ('Grün, Thomas', 18039, 'DE', 430, 150, 150, 130), ('Flügel, Clemens', 15806, 'DE', 400, 200, 160, 40), ('Öner, Sev', 7043, 'DE', 260, 90, 90, 80), ('Meier, Claus', 7704, 'DE', 180, 70, 78, 32), ('Kelly, Maria', 7199, 'DE', 170, 10, 70, 90), ('Munoz, Tabitha', 9632, 'US', 230, 150, 50, 30), ('White, Nicholas', 18199, 'US', 430, 60, 320, 50), ('Thompson, James', 16828, 'US', 400, 200, 180, 20), ('Gonzales, Cameron', 5509, 'US', 130, 50, 44, 36), ('Chander, Tommy', 3421, 'US', 120, 40, 30, 50), ('Green, Jack', 15971, 'UK', 380, 154, 77, 149), ('Lopez, Maria', 14533, 'UK', 340, 120, 160, 60), ('Stewart, Kimberly', 8668, 'UK', 200, 150, 10, 40), ('Patterson, Caroline', 5985, 'UK', 140, 40, 50, 50), ('Beasley, Shaun', 1511, 'UK', 30, 10, 5, 25); |
1. Extract query metadata
Before Sql Server 2012 you could use INFORMATION_SCHEMA to get metadata of database objects, but there was no way to extract metadata from arbitrary queries – this has changed. Sql Server now offers a dmv-query (dynamic management view) which takes a query statement and returns metadata about this query – without actually executing it. sys.dm_exec_describe_first_result_set operates on Tables, Views, Stored Procedures or Table-valued Functions. The full syntax is:
|
1 |
sys.dm_exec_describe_first_result(@tsql, @params, @include_browse_information) |
where @tsql is the query to execute, @params provides required parameters to the query (NULL if none) and @include_browse_information would have the query executed with the FOR BROWSE – option.
To get a list of field names and types, we call:
|
1 2 3 |
DECLARE @sql NVARCHAR(100) = N'SELECT * FROM sampleData20141215'; SELECT column_ordinal, name, system_type_name FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0) |
This returns self-explaining metadata about our query, which we can use to transform row data and produce table headers and a customized stylesheet.
Templating the html output
As a first step in our automated html conversion we have to agree on the general layout of the html code generated. As I said before, I want to have the output nicely formatted with optional text before and after the table. So the general layout would be:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @HtmlTemplate NVARCHAR(MAX) = ' <html> <head> {{css}} </head> <body> {{before}} <table> <thead> {{hdrRows}} </thead> <tbody> {{rows}} </tbody> </table> {{after}} </body> </html>'; |
The placeholders are in handlebars-notation only incidentally, so everybody knows what’s meant here. They will be replaced by generated text blocks later.
Produce the html table header from metadata
As a first exercise we can now generate the html to replace the {{hdrRows}} – placeholder directly from the metadata.
|
1 2 3 4 5 6 |
DECLARE @hdrRows nvarchar(max) = '' SELECT @hdrRows = COALESCE(@hdrRows + '<td>' + name + '</td>', '') FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS f SET @HtmlTemplate = REPLACE(@HtmlTemplate, '{{hdrRows}}', '<tr>' + @hdrRows + '</tr>') |
producing the following html in @hdrRows:
|
1 2 3 4 5 6 7 8 9 |
<tr> <td>subscriber</td> <td>subId</td> <td>country</td> <td>total</td> <td>articles</td> <td>discussions</td> <td>edits</td> </tr> |
(line breaks and indents for readability only)
2. Extract row data using dynamic sql and metadata
Now we are ready to extract row data and output it as html. Since we want to operate on standard queries (Select … From … ) and on Stored Procedures, we need to capture query results in a table variable to be processed. This can only be done with Dynamic SQL, like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @tbl TABLE ( [subscriber] nvarchar(50), [subId] int, [country] varchar(3), [total] int, [articles] int, [discussions] int, [edits] int); INSERT @tbl ([subscriber], [subId], [country], [total], [articles], [discussions], [edits]) SELECT * FROM sampleData20141215; SELECT @html = COALESCE(@html + '<tr><td>' + REPLACE(dbo.f_htmlEncode([subscriber]), '''', ' ') + '</td><td>' + REPLACE(dbo.f_htmlEncode([subId]), '''', ' ') + '</td><td>' + REPLACE(dbo.f_htmlEncode([country]), '''', ' ') + '</td><td>' + REPLACE(dbo.f_htmlEncode([total]), '''', ' ') + '</td><td>' + REPLACE(dbo.f_htmlEncode([articles]), '''', ' ') + '</td><td>' + REPLACE(dbo.f_htmlEncode([discussions]), '''', ' ') + '</td><td>' + REPLACE(dbo.f_htmlEncode([edits]), '''', ' ') + '</td></tr>' , '') FROM @tbl |
Above code is the SQL, which has to wrapped and executed as Dynamic SQL – the generator code is somewhat different, of course (see next code snippet). Here we produce the html for each table row based on the data in the sql server table’s records.
Each column is html-encoded (and converted to NVARCHAR by f_htmlEncode). For more information about html-encoding in T-Sql, see my article How to html-encode special characters in T-Sql. Apostrophes are removed. Results are concatenated and assigned to @html, which acts as OUTPUT variable.
And this is the full code to build these sql statements dynamically.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DECLARE @tblExpr NVARCHAR(MAX) = '' SELECT @tblExpr = COALESCE(@tblExpr + ', [' + name + '] ' + system_type_name, '') FROM sys.dm_exec_describe_first_result_set(@Sql, NULL, 0) DECLARE @insertExpr NVARCHAR(MAX) = '' SELECT @insertExpr = COALESCE(@insertExpr + ', [' + name + ']', '') FROM sys.dm_exec_describe_first_result_set(@Sql, NULL, 0) SET @tblExpr = 'DECLARE @tbl TABLE (' + STUFF(@tblExpr, 1,2,'') + ');' + 'INSERT @tbl (' + STUFF(@insertExpr, 1, 2, '') + ')' + @sql + ';' -- Convert all fields in query rows to table rows DECLARE @rowExpr NVARCHAR(MAX) = '' SELECT @rowExpr = COALESCE(@rowExpr + '''<td>'' + REPLACE(dbo.f_htmlEncode([' + name + ']), '''''''', '' '') + ''</td>'' ', '') FROM sys.dm_exec_describe_first_result_set(@Sql, NULL, 0) AS f WHERE name IS NOT NULL SET @rowExpr = 'SELECT @html = COALESCE(@html + ''<tr>'' + ' + REPLACE(@rowExpr, ' ''</td>'' ''<td>', ' ''</td>'' + ''<td>') + ' + ''</tr>'', '''') FROM @tbl' DECLARE @dynSql nvarchar(max) = @tblExpr + @rowExpr; DECLARE @rows nvarchar(max) = '' EXEC sp_executeSql @dynSql, N'@html NVARCHAR(MAX) = '''''''' OUTPUT', @html = @rows OUTPUT SET @HtmlTemplate = REPLACE(@htmlTemplate, '{{rows}}', @rows) |
At the end we execute the generated sql statement dynamically and insert results into the html template.
3. Create Stylesheet and Finalize Helper Proc
So, content is ready. This leaves us with the final task to format output properly. We do it with an inline stylesheet, which is placed in the head-section. Most of it is static – you may adapt it to your needs. But text-alignment and cell-widths depend on the field type, so we need to generate some styles from query metadata.
This is the final stylesheet:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
<style type="text/css"> body { font-size: 14px; font-family: Calibri, sans-serif; color: black; } table { border-collapse: collapse; } td { font-size: 14px; font-family: Calibri, sans-serif; color: black; border-style: solid; border-width: 1px; border-color: rgb(210,210,210); padding: 3px 10px 3px 10px; } thead > tr > td { background-color: rgb(180,180,255); font-size: 12px; } tbody > tr:nth-child(odd) { background-color: rgb(230,230,230); } tbody > tr:nth-child(even) { background-color: white; } td:nth-child(0n+2), td:nth-child(0n+4), td:nth-child(0n+5), td:nth-child(0n+6), td:nth-child(0n+7) { text-align: right; width: 60px; } |
The last style is applied to all numeric table columns. Please look up the generator code in the download.
4. Usage with database mail
The steps above are wrapped up in a single stored procedure, named p_ConvertAnyQueryToHtml. The calling syntax is:
|
1 2 3 4 5 6 7 |
EXEC p_ConvertAnyQueryToHtml @Sql NVARCHAR(MAX), @Script nvarchar(MAX), @Css nvarchar(MAX) = '', @Before nvarchar(MAX), @After nvarchar(MAX) = '', @Html nvarchar(MAX) = '' OUTPUT |
We call it, get the resulting string, attach it as body and send.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @sql NVARCHAR(100) = N'SELECT * FROM sampleData20141215'; DECLARE @before NVARCHAR(MAX) = ' <h1>Top 5 contributors</h1><strong>2nd Quarter 2014</strong> These are the top contributors from US, UK, Germany and Spain ' DECLARE @after NVARCHAR(MAX) = 'Job-ID: 123</p>' DECLARE @html NVARCHAR(MAX); EXEC p_ConvertAnyQueryToHtml @sql, '', '', @before, @after, @html OUTPUT EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Fill in your profile name here', @recipients = 'Fill in your recipients here', @subject = 'Top 5 contributors', @body_format = 'HTML', @body = @html, @importance = 'Normal'; |
Remember, that the actual display of results depends on the capabilities of your e-mail client.
Phew! Long post.
And here is the code download
References / Notes:
- T-SQL documentation on FOR XML: http://msdn.microsoft.com/en-us/library/ms178107.aspx – to concatenate row values
- T-SQL Stuff Function: http://msdn.microsoft.com/en-us/library/ms188043.aspx
- Concatenating Row Values with For XML / Replace: http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql
- Query Metadata: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/20/sql-server-v-next-denali-metadata-discovery.aspx
- Html Formatting: http://www.sqlservercentral.com/articles/T-SQL/99398/. This article kicked me off on the topic
- Some posts on html-formatting a table: http://www.sqlservercentral.com/Forums/Topic1465444-279-1.aspx
- Easy way to format ss table as html: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/10/24/format-query-output-into-an-html-table-the-easy-way.aspx
- Even SS-MVPs use Cursors to process tables row by row: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/03/28/send-table-or-view-as-embedded-html-lt-table-gt-in-an-email-stored-procedure.aspx (well in 2007, some did)
- Another nice kickoff question on stackoverflow: http://stackoverflow.com/questions/7070053/convert-a-sql-query-result-table-to-an-html-table-for-email