Home » Export SQL Server query results to HTML table via PowerShell

Export SQL Server query results to HTML table via PowerShell

by Vlad Drumea
0 comment

In a previous post, I’ve covered how to import and query HTML table data in SQL Server, so I figured it makes sense to also show ho to export SQL Server query results to an HTML table using PowerShell and dbatools.

This is really similar to how the HTML report from PSBlitz is generated. The differences being that PSBlitz doesn’t rely on dbatools and there’s a bit more formatting and some JavaScript involved.

Environment

You just need dbatools installed, and the example relies on the AdventureWorks2019 database.

Why?

This is a great option for outputting data to a portable format that can be viewed on pretty much anything, and doesn’t really allow for accidental editing.
And, if your reporting requirements aren’t worth setting up something like an SSRS server, you can set up a periodically refreshing dashboard using this, an IIS server and some scheduled tasks or SQL Server Agent jobs.

How to do it

HTML doctype, header and CSS

First, I load the HTML doctype declaration, header related info, CSS (so that we won’t end up with an extremely bland table), and the start of the body tag in a variable to use later one.

The CSS will tell browsers how the table and its components should be displayed.
Controlling everything from background color to highlighting rows on cursor hover.

Query and columns

This would all be pointless without an actual query to retrieve the desired result set.
So, I load that in a variable too.

And I also load the list of columns that I want to be exclude from the resulting data set.
Notice how those columns aren’t really part of the above query? I’ll come back to that later.

Generating the HTML table

Now I can use Invoke-DbaQuery to get the result set and then use Select-Object together with Convert-ToHTML to generate an HTML table.

At this point, the $htmlTable variable contains only the HTML table itself.

Terminal window showing the above command and the contents of the $htmlTable variable that's a HTML table object SQL Server query result HTML PowerShell

Putting it all together

All that’s left now is to build the contents of the HTML file and write it to disk.

In order to do that, I join the contents of the $HtmlPre variable (doctype, header, and CSS) with the H1 header, and the table data. I then add a line break and close the tags opened in $HtmlPre.

Now, to write it to an actual HTML file.

The end result

The resulting HTML file can be viewed in any browser.


It’s all in a nice formatted table in a nice and portable format, which, with a bit of CSS, allows for neat effects such as highlighting the row you’re currently hovering over.

But why did I pipe to Select-Object?

Now, to demo what happens without throwing Select-Objects in the mix.

The command would look like this:

And the resulting HTML file would have 5 additional columns that we don’t really want.

Browser window showing the resulting HTML file but this time it contains 5 additional columns: "RowError", "RowState", "Table", "ItemArray", "HasErrors" SQL Server query result HTML PowerShell

Conclusion

Exporting SQL Server query results to HTML using PowerShell is a nifty way to generate “quick and dirty” reports and dashboards, and also a nice little PowerShell exercise to get data professionals more into PowerShell.

And people with a little more HTML and CSS knowledge than I have (I barely have enough to do this demo 🙂 )can make the results look even better.

You may also like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.