Execute-TSqlCodeAnalysis.ps1

# Wrapper script for T-SQL code analysis rules. 
# 
# 1) installs the latest Microsoft.SqlServer.DacFx.x64 NuGet package for the current user (if needed)
# 2) executes Add-Type for the Microsoft.SqlServer.TransactSql.ScriptDom.dll assembly for use by code analysis script
# 3) dot-sources code analysis PS function script
# 4) executes code analysis rules
#
# Example usage: 
#     powershell -ExecutionPolicy RemoteSigned -File "C:\PowerShellScripts\Execute-TSqlCodeAnalysis.ps1" -scriptFilePath = "C:\SqlScripts\YourScript.sql"
#
param (
    $scriptFilePath
) 

Function Load-TSqlScriptDom() {

    # create trusted NuGet package source, if needed
    $packageSource = Get-PackageSource | where { ($_.Location -EQ "https://www.nuget.org/api/v2") -and ($_.ProviderName -eq "NuGet") -and ($_.IsTrusted -eq $true) }
    if($packageSource -eq $null) {
        Register-PackageSource NuGetV2 https://www.nuget.org/api/v2 -ProviderName NuGet -Trusted
    }

    # install package, if needed
    $dacFxPackage = Install-Package Microsoft.SqlServer.DacFx.x64 -Source ($packageSource.Name) -Scope CurrentUser # scope AllUsers requires admin
    if($dacFxPackage -eq $null) {
        # package already installed - get package
        $dacFxPackage = Get-Package Microsoft.SqlServer.DacFx.x64
    }
    else {
        throw "Microsoft.SqlServer.DacFx.x64 NuGet package not found"
    }

    # load Microsoft.SqlServer.TransactSql.ScriptDom.dll into app domain for use in PS scripts
    $packageFolderPath = [System.IO.Path]::GetDirectoryName($dacFxPackage.Source)
    Add-Type -LiteralPath "$packageFolderPath\lib\net46\Microsoft.SqlServer.TransactSql.ScriptDom.dll"

}

############
### main ###
############

try {

    Load-TSqlScriptDom
    # dot-source code analysis PS function after script dom assembly is loaded
    . $PSScriptRoot\Find-NoLockHints.ps1 # code analysis in same folder as this script ($PSScriptRoot)
    # run code analysis function
    Find-NoLockHints -ScriptFilePath $scriptFilePath

}
catch {
    throw
}

Find-NoLockHints.ps1

# Find NOLOCK hints in a T-SQL script using TMicrosoft.SqlServer.TransactSql.ScriptDom.dll.
# The assembly needs to first be loaded into the current app domain using Add-Type because
# the visitor class derives from a type defined in that assembly.

Function Find-NoLockHints ($scriptFilePath) {

    try {

        class LockHintVisitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlConcreteFragmentVisitor {

            [bool]$NoLockFound = $false

            [void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.TableHint] $fragment) {
                if($fragment.HintKind -eq [Microsoft.SqlServer.TransactSql.ScriptDom.TableHintKind]::NoLock) {
                    Write-Host "$($fragment.HintKind) found at line $($fragment.StartLine), column $($fragment.StartColumn)"
                    $this.NoLockFound = $true
                }
            }
    
        }

        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true)
        $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
        $script = [System.io.file]::ReadAllText($scriptFilePath)
        $stringReader = New-Object System.IO.StringReader($script)

        $frament = $parser.Parse($stringReader, [ref]$parseErrors)
        if($parseErrors.Count -gt 0) {
            throw "$($parseErrors.Count) parsing errors: $(($parseErrors | ConvertTo-Json))"
        }

        $visitor = [LockHintVisitor]::new()
        $frament.Accept($visitor)
        if($visitor.NoLockFound) {
            Write-Host "One or more NOLOCK hints found"
        }
        else {
            Write-Host "No NOLOCK hints found"
        }

    }
    catch {
        throw
    }

}