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
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:
- Use Test-SqlScript in conjunction with a source control check-in to verify the script is valid
- Use Out-SqlScript to “pretty-print” ugly SQL script within SQL Server Management Studio
To setup the formatter within SQL Server Management Studio:
- Install the SQLParser snapin using Init-SqlParser.ps1 script included in the download
- Add Add-PSSnapin to your profile
- Create a bat file called formatSql.bat (also included in the download)
- In SQL Server Management Studio go to Tools => External Tools
- Configure a new external tool as shown.
- 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.