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

SELECT TOP 10 name,create_date
FROM msdb.sys.procedures
ORDER BY create_date

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

name create_date
sp_sqlagent_is_srvrolemember 9/29/2010 10:13:10 AM
sp_verify_category_identifiers 9/29/2010 10:13:10 AM
sp_verify_proxy_identifiers 9/29/2010 10:13:10 AM
sp_verify_credential_identifiers 9/29/2010 10:13:10 AM
sp_verify_subsystems 9/29/2010 10:13:11 AM
sp_verify_subsystem_identifiers 9/29/2010 10:13:11 AM
sp_verify_login_identifiers 9/29/2010 10:13:11 AM
sp_verify_proxy 9/29/2010 10:13:11 AM
sp_add_proxy 9/29/2010 10:13:11 AM
sp_delete_proxy 9/29/2010 10:13:11 AM

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

SELECT TOP 10 '<tr><td>',name,'</td><td>',create_date,'</td></tdr>'
FROM msdb.sys.procedures
ORDER BY create_date

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

Param($InstanceName)

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$InstanceName;Database=master;Integrated Security=True"


$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandTimeout = 0
$SqlCmd.Connection.Open()



$SqlCmd.CommandText = "SELECT TOP 10 name,create_date
                        FROM msdb.sys.procedures
                        ORDER BY create_date"
                        
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)


$FilePath    = "C:PowerShellOutput" + $InstanceName+ "_formattedOutput.htm"
$Title = $InstanceName+ " Formatted Results for Blog"
$DataSet.Tables[0]  | convertto-html -property name, create_date -title $Title > $FilePath

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

powershell.exe "D:PowershellFormatOutput.ps1" -InstanceName localhost

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>localhost FreeSpace</title>
</head><body>
<table>
<colgroup>
<col/>
<col/>
</colgroup>
<tr><th>name</th><th>create_date</th></tr>
<tr><td>sp_sqlagent_is_srvrolemember</td><td>9/29/2010 10:13:10 AM</td></tr>
<tr><td>sp_verify_category_identifiers</td><td>9/29/2010 10:13:10 AM</td></tr>
<tr><td>sp_verify_proxy_identifiers</td><td>9/29/2010 10:13:10 AM</td></tr>
<tr><td>sp_verify_credential_identifiers</td><td>9/29/2010 10:13:10 AM</td></tr>
<tr><td>sp_verify_subsystems</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_verify_subsystem_identifiers</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_verify_login_identifiers</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_verify_proxy</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_add_proxy</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_delete_proxy</td><td>9/29/2010 10:13:11 AM</td></tr>
</table>
</body></html>

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

<div class="tables">
<table>
<! Paste stuff here  !>
</table>
</div>

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

<div class="tables">
<table>
<tr><th>name</th><th>create_date</th></tr>
<tr><td>sp_sqlagent_is_srvrolemember</td><td>9/29/2010 10:13:10 AM</td></tr>
<tr><td>sp_verify_category_identifiers</td><td>9/29/2010 10:13:10 AM</td></tr>
<tr><td>sp_verify_proxy_identifiers</td><td>9/29/2010 10:13:10 AM</td></tr>
<tr><td>sp_verify_credential_identifiers</td><td>9/29/2010 10:13:10 AM</td></tr>
<tr><td>sp_verify_subsystems</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_verify_subsystem_identifiers</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_verify_login_identifiers</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_verify_proxy</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_add_proxy</td><td>9/29/2010 10:13:11 AM</td></tr>
<tr><td>sp_delete_proxy</td><td>9/29/2010 10:13:11 AM</td></tr>
</table>
</div>

And that will then look like this

name create_date
sp_sqlagent_is_srvrolemember 9/29/2010 10:13:10 AM
sp_verify_category_identifiers 9/29/2010 10:13:10 AM
sp_verify_proxy_identifiers 9/29/2010 10:13:10 AM
sp_verify_credential_identifiers 9/29/2010 10:13:10 AM
sp_verify_subsystems 9/29/2010 10:13:11 AM
sp_verify_subsystem_identifiers 9/29/2010 10:13:11 AM
sp_verify_login_identifiers 9/29/2010 10:13:11 AM
sp_verify_proxy 9/29/2010 10:13:11 AM
sp_add_proxy 9/29/2010 10:13:11 AM
sp_delete_proxy 9/29/2010 10:13:11 AM

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