SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

A PowerShell Pester Check for parsing SQL scripts

I like to write Pester checks to make sure that all is as expected! This is just a quick post as much to help me remember this script ??

This is a quick Pester test I wrote to ensure that some SQL Scripts in a directory would parse so there was some guarantee that they were valid T-SQL. It uses the SQLParser.dll and because it was using a build server without SQL Server I have to load the required DLLs from the dbatools module (Thank you dbatools ?? )

It simply runs through all of the .sql files and runs the parser against them and checks the errors. In the case of failures it will output where it failed in the error message in the failed Pester result as well.

You will need dbatools module installed on the instance and at least version 4 of the Pester module as well

Describe "Testing SQL" {
    Context "Running Parser" {
        ## Load assembly
        $Parserdll = (Get-ChildItem 'C:\Program Files\WindowsPowerShell\Modules\dbatools' -Include Microsoft.SqlServer.Management.SqlParser.dll -Recurse)[0].FullName
        [System.Reflection.Assembly]::LoadFile($Parserdll) | Out-Null
        $TraceDll = (Get-ChildItem 'C:\Program Files\WindowsPowerShell\Modules\dbatools' -Include Microsoft.SqlServer.Diagnostics.Strace.dll -Recurse)[0].FullName
        [System.Reflection.Assembly]::LoadFile($TraceDll) | Out-Null
        $ParseOptions = New-Object Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions
        $ParseOptions.BatchSeparator = 'GO'
        $files = Get-ChildItem -Path $Env:Directory -Include *.sql -Recurse ## This variable is set as a Build Process Variable or put your path here
        $files.ForEach{
            It "$($Psitem.FullName) Should Parse SQL correctly" {
                $filename = $Psitem.FullName
                $sql = Get-Content -LiteralPath "$fileName"
                $Script = [Microsoft.SqlServer.Management.SqlParser.Parser.Parser]::Parse($SQL, $ParseOptions)
                $Script.Errors | Should -BeNullOrEmpty
            }
        }
    }
}

SQL DBA With A Beard

Rob is a SQL Server DBA currently supporting several hundred databases ranging from SQL 2000 to SQL 2012. Rob has a fabulous beard and loves to use Powershell to make his life easier.

Comments

Leave a comment on the original post [sqldbawithabeard.com, opens in a new window]

Loading comments...