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
}
}
}
}
nice, how do you deal with warnings if any. ie. dependencies on other modules?
In this script not at all because I have control and know that #dbatools ous I the machine running the test.
If you wanted to, you could write a pester script that checks for the existence of modules your script is dependent on.
Pingback: Parsing T-SQL Scripts With Pester – Curated SQL
Did dbatools just remove the dll’s in their latest releases?
Not to my knowledge, which version do you have ?
I had the issue with 0.9.534. But I just updated to 0.9.704 and I don’t see the dlls as well.