The SQL Looks good but does it parse?

In honour of Dave Ballantyne’s excellent article ‘Cleaning Up SQL Server Deployment Scripts‘, here is a PowerShell routine that checks to see if your text-based SQL scripts parse properly. If you’ve checked them in from SQL Source Control, they almost certainly will parse correctly, though you can run the parser at SQL Server 2008 mode or SQL Server 2012 mode by changing the variable $ParseOptions.TransactSqlVersion in order to check whether 2012 script will run on a 2008 server, so it is not quite as silly as it might sound. Also, it is perfectly possible to check a script into source control from outside the SSMS environment in which case it may not compile, and so it will be well worth using to check for this. This PowerShell script checks mountains of scrips ridiculously fast. It is far faster than any other way I can imagine.

In this script, I check a directory and all the subdirectories, since I store my scripts with each server in its own directory, database with build script and database level scripts each in their own subdirectories and usually routines filed in subdirectories according to type. Heaven only knows how you store your scripts, but you can easily alter the PowerShell to suit your setup. I leave an error report in the root directory with all errors documented in it.

Curiously, the parser method doesn’t seem to have any other obvious use besides this, because it is impossible to get at the individual tokens as far as I’m aware (The deprecated DataDude version of the parser method at Microsoft.SqlServer.SqlParser.SqlCodeDom.SqlScript passed back an array of tokens, which was very nice to have.) As Dave gave instructions of how to get hold of the SQLParser (you may have it already) I won’t repeat the instructions here.