SQL SERVER: How to get the stored procedure text EXCLUDING comments? I need to find all the stored procs that use Transactions, as I want to enable transaction abort to those procedures.  However, --I didn't do this; it's inherited-- many of  the stored procedures contain testing procedures with in comment blocks and most of the tests contain transaction blocks. I am only interested in changing stored procs that actually use transactions. AND I want to be able to monitor when stored procs are updated so that I can make sure that this flag is set.

SET XACT_ABORT ON;


Addendum: based on comments, here're some examples from my system.

/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/

/*
Use case: 147
BEGIN TRANSACTION
....
*/
Below is a PowerShell example that uses the Microsoft.SqlServer.TransactSql.ScriptDom to parse procs and identify those with BEGIN TRAN statements. This version will download the assembly from NuGet if Microsoft.SqlServer.TransactSql.ScriptDom.dll doesn't already exist in the specified location. You could use a package manager instead for that task.

param (
    # Location of Microsoft.SqlServer.TransactSql.ScriptDom.dll assembly.
    $scriptDomAssemblyPath = "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll",
    # Url of Microsoft.SqlServer.DacFx.x64 in official Microsoft NuGet repository.
    $scriptDomNuGetUrl = "https://www.nuget.org/api/v2/package/Microsoft.SqlServer.DacFx.x64/150.4200.1"
)

$procQuery = @"
SELECT
      QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(p.name) AS ProcedureName
    , m.definition
FROM sys.procedures AS p
JOIN sys.sql_modules AS m ON m.object_id = p.object_id;
"@

# Add Microsoft.SqlServer.TransactSql.ScriptDom assembly, downloading if needed
Function Add-ScriptDomAssemblyType ($scriptDomAssemblyPath, $scriptDomNuGetUrl) {

    # if T-SQL script DOM assembly does not exist, download from NuGet and extract assembly from package to $scriptDomAssemblyPath
    if(![System.IO.File]::Exists($scriptDomAssemblyPath)) {
        if([String]::IsNullOrWhiteSpace($scriptDomNuGetUrl)) {
            throw "Script DOM assembly not found at $scriptDomAssemblyPath and NuGet package download Url not specified"
        }
        $response = Invoke-WebRequest -Uri $scriptDomNuGetUrl
        if ($response.StatusCode -ne 200) {
            throw "Unable to download Microsoft.SqlServer.TransactSql.ScriptDom NuGet package: $response.StatusCode : $response.StatusDescription"
        }
        $tempZipFilePath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName() + ".zip")
        [System.IO.File]::WriteAllBytes($tempZipFilePath, $response.Content)
        $response.BaseResponse.Dispose()
        $tempUnzipFolderPath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName())
        Expand-Archive -Path $tempZipFilePath -DestinationPath $tempUnzipFolderPath
        $tempZipFilePath | Remove-Item
        Move-Item "$tempUnzipFolderPath\lib\net46\Microsoft.SqlServer.TransactSql.ScriptDom.dll" "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
        $tempUnzipFolderPath | Remove-Item -Recurse
    }

    # load assembly into app domain so it can be used in this script
    Add-Type -Path $scriptDomAssemblyPath
}

# parse CREATE PROCEDURE script, returning true if it contains a BEGIN TRAN T-SQL statement
Function Test-HasBeginTrasaction ($procText) {

    # use TSql parser appropriate for targetted SQL Server version
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
    $scriptReader = New-Object System.IO.StringReader($procText)
    $fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0)
    {
        # parsing errors may occur for existing stored procs if the code is invalid due to obsolete T-SQL syntax
        throw "$($parseErrors.Count) parsing errors: $( ( $parseErrors | ConvertTo-Json ) )"
    }

    # evaluate each statement in stored procedure
    foreach ($statement in $fragment.Batches[0].Statements[0].StatementList.Statements) {
        # return true if a BeginTransactionStatement is found
        if ($statement.GetType().Name -eq "BeginTransactionStatement") {
            return $true
        }
    }

    # return false if no BeginTransactionStatement is found
    return $false

}

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

try {

    Add-ScriptDomAssemblyType -scriptDomAssemblyPath $scriptDomAssemblyPath -scriptDomNuGetUrl $scriptDomNuGetUrl

    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($procQuery, $connection)
    $connection.Open()
    $reader = $command.ExecuteReader()
    while($reader.Read()) {

        $hasBeginTransaction = Test-HasBeginTrasaction -procText $reader["definition"]

        if( $hasBeginTransaction ) {
            Write-Host "Has BEGIN TRANSACTION: $($reader["ProcedureName"])"
        }

    }
    $reader.Close()
    $connection.Close()

}
catch {
    throw
}


This can be easily extended to also check for SET XACT_ABORT ON in order to include only those procs that need attention, assuming SET XACT_ABORT ON is properly placed in the proc code. Below is an additional function and body code to do this.

# parse CREATE PROCEDURE script, returning true if it contains a SET XACT_ABORT ON
Function Test-HasSetXactAbortOn ($procText) {

    # use TSql parser appropriate for targetted SQL Server version
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
    $scriptReader = New-Object System.IO.StringReader($procText)
    $fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0)
    {
        # parsing errors may occur for existing stored procs if the code is invalid due to obsolete T-SQL syntax
        throw "$($parseErrors.Count) parsing errors: $( ( $parseErrors | ConvertTo-Json ) )"
    }

    # evaluate each statement in stored procedure
    foreach ($statement in $fragment.Batches[0].Statements[0].StatementList.Statements) {
        # return true if a SET XACT_ABORT ON is found
        if (($statement.GetType().Name -eq "PredicateSetStatement") -and
            ($statement.Options -band [Microsoft.SqlServer.TransactSql.ScriptDom.SetOptions]::XactAbort) -and
            $statement.IsOn ) {
            return $true
        }
    }

    # return false if no SET XACT_ABORT ON is found
    return $false

}

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

try {

    Add-ScriptDomAssemblyType -scriptDomAssemblyPath $scriptDomAssemblyPath -scriptDomNuGetUrl $scriptDomNuGetUrl

    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($procQuery, $connection)
    $connection.Open()
    $reader = $command.ExecuteReader()
    while($reader.Read()) {

        $hasBeginTransaction = Test-HasBeginTrasaction -procText $reader["definition"]
        $hasSetXactAbortOn = Test-HasSetXactAbortOn -procText $reader["definition"]

        if( $hasBeginTransaction -and !$hasSetXactAbortOn ) {
            Write-Host "Has BEGIN TRANSACTION without SET_XACT_ABORT ON: $($reader["ProcedureName"])"
        }

    }
    $reader.Close()
    $connection.Close()

}
catch {
    throw
}