Display SELECT query or table as HTML
![]() |
This post is part of #tsql2sday event :) |
Even when we have already in place a custom inventory and monitoring solution (BTW done using C#, asp.net, SQL Server and Powershell), a couple of rogue servers, needs their own special treat, most of the cases this has to do with some kind of monitoring.
So, for these serves, the solution is to implement local jobs to monitor different kinds of stuff and send via email the monitoring and usage results.
We could perfectly send the info in plain text, but reading this in an email, especially when your boss is included in the distribution list is just awful, so we had to format the data first to make it look better...
That's why I created sp_TabletoHTML, a Stored Procedure to convert any table or SELECT query to an HTML table, so you just have to paste the output in your email body.
Before this stored procedure, we had to do the HTML conversion based on each table columns, and after 3 or 4 times of adapting the same code, I was tired and ended up doing this stored procedure.
How it works?
- Create the stored procedure and remember where you left it (can be the master database, it really doesn't matter as long as you can access it properly).
- Then you pass in @stTable (yes, Unicode varchar in case you have weird characters in your object names) the name of the table or view containing the data using the schema.name convention or you can also pass a SELECT query sentence, giving you the ability of formatting or cleaning up your data before converting it to HTML.
- You can specify if want to include column names or not (included by default) or the predefined style you want or no style at all (or leave the parameters at their defaults)
- Execute the stored procedure and use the output string as your HTML <table> result.
- Enjoy!
Sample execution:
SET @SQLSentence = 'SELECT name, state_desc, create_date, collation_name FROM sys.databases' EXEC sp_TabletoHTML @stTable = @SQLSentence, @TableStyle = 3, @RawTableStyle = @st OUTPUT
And you will obtain a beautiful looking HTML table like this one:
Formats can vary... |
What is the logic involved?
This is the core of the formatting, all the columns with the same name of TD that will translate into <td></td> and the rows that will translate into elements named <tr></tr>:
SELECT [COL1] as TD, [COL2] as TD... FROM ##rowstablePreHTML FOR XML RAW('TR'), ELEMENTS
Since some of the queries are build dynamically, I use sp_executesql, that could be a security risk for untrusted sources.
For internal use, we did not check for malicious code, because... I trust my own code, but before releasing it into Github I added a very basic malicious code check (just check for keywords that has nothing to do with table results) and raise an error for any occurrence encountered. As a recommendation, always validate your queries first.
Null values are not converted to an empty string (because of the unique way XML handle their nulls). Before using it, remove null values from your data.
Some special datatypes like geography, timestamp, XML, image are not supported, if you try to use them, an error will raise, remove these columns before using it.
Where is the code?
![]() |
Fortunately for you, it is free! |
I offer this stored procedure as open source in GitHub, you can check it here.
The link is also in the Free Tools section on this blog.
The full documentation is also available on the Github page, so you can check the parameter usage and some other examples there!
Also, if any of you can improve this SP, please do it, you are more than welcome!
>> security risk for untrusted sources
ReplyDeleteCan't you just replace code like
SET @SelectStatement = 'SELECT tbl1.* into ##rowstablePreHTML FROM (' + @stTable+') tbl1'
with a "safe" tablename e.g. @stTable_SAFE
SET @stTable_SAFE = REPLACE(QuoteName(@stTable), '''', '''''')
instead of checking for possible SQL Injection keywords (which is unlikely to ever be future-safe ...)
Similarly I suggest that
SELECT '[' + COLUMN_NAME + ']' AS 'TH'
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
should use QuoteName instead of adding Square Bracket, and replace any embedded single-quote with double-single-quote
SELECT REPLACE(QuoteName(COLUMN_NAME), '''', '''''') AS 'TH'
thanks for your recommendations! with other point of view i can make my code better, I will try to implement your recommendations.
Delete