﻿#requires -version 5.0
#requires -modules dbatools,pester

<#
.Synopsis
  Ensure that some SQL Scripts in a directory would parse so there was some guarantee that they were valid T-SQL.

.OUTPUTS
  Any unvalid TSQL

.NOTES
   Original link: https://sqldbawithabeard.com/2018/07/25/a-powershell-pester-check-for-parsing-sql-scripts/
   Author: Rob Sewell
#>

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
            }
        }
    }
}

Add Parse TSQL powershell script
Add Parse TSQL powershell script

