Test-SqlScript and Out-SqlScript cmdlets

I ‘ve been using  Visual StudioTeam System 2008 Database Edition (VSDB) and noticed an interesting post on Gert Drapers’ blog, entitled Getting to the Crown Jewels. In the posting Gert demonstrates a basic C# WinForm application which parsers and formats T-SQL using the assemblies Microsoft.Data.Schema.ScriptDom and Microsoft.Data.Schema.ScriptDom.Sql included in VSDB. A subsequent posting states if you own an official copy of Visual Studio Team System 2008 Database Edition GDR, which I do, you are allowed to redistribute these assemblies. Looking at the WinForm code, I thought this would make a couple of interesting cmdlets. So, I created two cmdlets called Test-SqlScript and Out-Sqlscript. Test-SqlScript parses a T-SQL script and tests whether the script is valid. Out-SqlScript in addition to validating the script, re-formats the script output with 25 different formatting options. I created help files with the cmdlets, you can see the formating options by using get-help Out-SqlScript.
 You might ask, how can I use these cmdlets? Well I can immediately think of two use cases:
  1. Use Test-SqlScript in conjunction with a source control check-in to verify the script is valid
  2. Use Out-SqlScript to "pretty-print" ugly SQL script within SQL Server Management Studio

To setup the formatter within SQL Server Management Studio:

  1. Install the SQLParser snapin using Init-SqlParser.ps1 script included in the download
  2. Add Add-PSSnapin to your profile
  3. Create a bat file called formatSql.bat (also included in the download)
  4. In SQL Server Management Studio go to Tools => External Tools
  5. Configure a new external tool as shown.
  6. To use the new external tool ensure you highlight the text in SQL Server Management Studio you want to format.

It would have been nice to take the T-SQL parsing routine a step further and build an object dependency list from a SQL script, but unfortunately the method/properties for getting to the referenced objects appear to be private.

 The cmdlets will be included in the next release of SQL Server Powershell Extensions. In the meantime I’ve included compiled cmdlets here until I can get a release packaged up. The source code is available on the CodePlex project site Source Code section.
This entry was posted in PowerShell. Bookmark the permalink.

3 Responses to Test-SqlScript and Out-SqlScript cmdlets

  1. Steven says:

    You rock! These are awesome cmdlets.

Comments are closed.