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
}
. I am only interested in changing stored procs that actually use transactions- are you looking for an explicitbegin tran .. end tran?