If you are a blogger, you want to spend time on writing the content not on formatting tables and prettifying syntax so that it looks nice. The syntax is taken care of because we use the GeSHi syntax highlighter. formatted output is another story

If you want to show the output of a query in a html table you have a couple of options. Let's first take a look at a simple query

You can of course use the PRE tag to format that somewhat, here is what it looks like

name	                             create_date
sp_sqlagent_is_srvrolemember	     2010-02-06 04:12:12.100
sp_verify_category_identifiers	     2010-02-06 04:12:12.100
sp_verify_proxy_identifiers	     2010-02-06 04:12:12.107
sp_verify_credential_identifiers     2010-02-06 04:12:12.110
sp_verify_subsystems	             2010-02-06 04:12:12.130
sp_verify_subsystem_identifiers	     2010-02-06 04:12:12.137
sp_verify_login_identifiers	     2010-02-06 04:12:12.140
sp_verify_proxy	                     2010-02-06 04:12:12.143
sp_add_proxy	                     2010-02-06 04:12:12.150
sp_delete_proxy	                     2010-02-06 04:12:12.157

What I want is a table, here is what that looks like

In order to accomplish that, I can grab the query result and put TD and TR tags around the columns and rows. I can also do something like the following

But what if it is a stored proc like xp_fixeddrives? You can of course also use Excel, paste the results in there, add columns and put the TD and TR tags in those columns. but there is a better/easier/faster way. You can use PowerShell to do this for you, you can use the ConvertTo-Html Cmdlet

Here is a simple powershell script that accepts an instance parameter, runs the query and then generates a html file as output

Save the file as FormatOutput.ps1, I have it saved in my D:\Powershell\ folder, the file will save the output in my C:\PowerShellOutput\ folder

The script has a parameter that is the name of the SQL Server instance that you want to run the query against, you specify that with -InstanceName after the filename and you then supply the name of your instance, in my case it is localhost

Run the PowerShell script like this

You can run it from a command prompt or from the PowerShell Integrated Scripting Environment (ISE)

Now go in your folder where the file has been created, if you open it in notepad or if you do view source when you open it in a browser, you will see something like this

All I have to do now is create this part of the code in my blogpost

I then copy and paste the rows from the html table between the table tags on line 3 in the previous code example

Here is what I end up with

And that will then look like this

Pretty cool and simple

Next steps
I am no PowerShell expert and I am sure that the script in this post can be improved, leave me a comment if you see a major mistake

Of course you don't want to hard code the query either, you could make the script generic so that you can supply the query as a parameter instead