Find-SyntaxErrors

  <#  
.SYNOPSIS  
    Will check passed script for syntax errors
.DESCRIPTION  
    Will display syntax errors if any
.NOTES  
    Author     : Mala Mahadevan (malathi.mahadevan@gmail.com)
  
.PARAMETERS
-Script: text file containing T-SQL
.LIMITATIONS
Can only check for syntax, not validate objects since it is asynchronous and not connected to any sql server
.LINK  
    
 
.HISTORY
2022.01.25 V 1.00
#>

function Find-SyntaxErrors
{
 [CmdletBinding()]
    param(
           $Script
           )
try {


    
     # load Script DOM assembly for use by this PowerShell session
    Add-Type -Path "C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
    
    If ((Test-Path $Script -PathType Leaf) -eq $false)
    {
        $errormessage = "File $Script not found!"
        throw $errormessage
    }
    
    #Use the parser that is best suited to your compat level.
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true)
    #Set object to capture errors if any
    $SyntaxErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
    
    #Set object to read script linewise
    $stringreader = New-Object -TypeName System.IO.StreamReader -ArgumentList $Script

    #Building the abstract syntax tree
    $tSqlFragment = $parser.Parse($stringReader, [ref]$SyntaxErrors)

    #If any syntax errors are found display
    if($SyntaxErrors.Count -gt 0) {
        throw "$($SyntaxErrors.Count) parsing error(s): $(($SyntaxErrors | ConvertTo-Json))"
    }
    else
    {
        write-host "No Syntax errors found in $($Script)!" -backgroundcolor Green
    }

}    
catch {
    throw
}

}