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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.SqlParser") | Out-Null $ParseOptions = New-Object Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions $ParseOptions.BatchSeparator = 'GO' #what do you want your batch separator to be $ParseOptions.CompatibilityLevel = [Microsoft.SqlServer.Management.SqlParser.Common.DatabaseCompatibilityLevel]::Current # the database compatibility level of the SQL Server parser. $ParseOptions.IsQuotedIdentifierSet = $true #whether quoted strings are treated as identifiers or as strings. $ParseOptions.TransactSqlVersion = [Microsoft.SqlServer.Management.SqlParser.Common.TransactSqlVersion]::Current # the TransactSqlVersion to be parsed against: Current,Version105 (2008 R2.) or Version110 (2012) set-psdebug -strict #Create the object $workspace = "E:\MyScriptsDirectory" $null > "$workspace\Errors.txt" $errors = 0 get-childitem -recurse -path "$workspace" | #get every SQL file in this directory and all subdirectories where-object -filterscript { ($_.Name -like '*.SQL') } | #just the SQL files please Foreach-object { #and for each one... $SQL = get-content $_.VersionInfo.filename | foreach-object{ "$_`n" } #get the text of the file "parsing $($_.VersionInfo.filename)" #parse each file to check for errors $Script = [Microsoft.SqlServer.Management.SqlParser.Parser.Parser]::Parse($SQL, $ParseOptions) $errors += $script.Errors.count #and remember how many errors there were foreach ($error in $script.Errors) #save the error report to an error log { if ($error.IsAWarning -eq $false) { $theMessage = 'Error: ' + $Error.Message } else { $theMessage = 'Error: ' + $Error.Message }; "In $($_.VersionInfo.filename) line/Column Start:$($error.Start), End:$($Error.End) $($Error.Type) $theMessage " >> "$workspace\Errors.txt" } } if ($errors -gt 0) { #if there were errors then report them $errorReport = get-content "$workspace\Errors.txt" | foreach-object{ "$_`n" }; "Master, there were $errors errors in $workspace! `n $errorReport" } #and display them else { "All was well in $workspace" } |
Load comments