如何批量更新数百个T-SQL存储过程，在指定位置添加commit语句？

需求背景 #

需要批量为数百个T-SQL存储过程中的@p_TEXT = @temp;语句后追加commit;。

现有方案的局限 #

之前基于sys.syscomments生成ALTER语句的方案存在两个核心问题：

• 超长存储过程无法处理：sys.syscomments.text字段长度限制为4000字符，必须添加LEN(text) < 4000过滤条件，超出长度的存储过程只能手动修改。
• 注释导致ALTER语句出错：存储过程代码被拼接为单行后，若存在--单行注释，注释后的所有内容会被当作注释，导致生成的ALTER语句逻辑失效。

改进方案 #

方案1：用sys.sql_modules替代sys.syscomments（解决超长问题） #

sys.sql_modules.definition字段为nvarchar(max)，支持存储完整的存储过程代码，无需担心长度限制。同时可通过简单规则初步排除注释中的目标语句：

SELECT 
    CONCAT(
        REPLACE(
            REPLACE(m.definition, 'CREATE PROCEDURE', 'ALTER PROCEDURE'),
            '@p_TEXT = @temp;', 
            '@p_TEXT = @temp;commit;'
        ),
        CHAR(10), 'GO'
    ) AS AlterScript
FROM sys.procedures p
JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE m.definition LIKE '%@p_TEXT = @temp;%'
  -- 初步过滤单行注释中的目标语句（简单场景有效，复杂嵌套注释需额外处理）
  AND NOT m.definition LIKE '%--%@p_TEXT = @temp;%'

说明：上述注释过滤仅适用于简单单行注释场景，若存在嵌套注释或注释与目标语句跨多行的情况，需补充更复杂的字符串处理逻辑。

方案2：PowerShell脚本（适配复杂场景） #

针对复杂注释、超长代码的场景，用PowerShell脚本读取存储过程定义，保留原有格式进行精准修改，再生成并执行ALTER脚本：

# 配置数据库连接信息
$serverName = "YourServerName"
$dbName = "YourDatabaseName"

# 获取需要修改的存储过程列表
$procs = Invoke-SqlCmd -ServerInstance $serverName -Database $dbName -Query @"
SELECT name, definition 
FROM sys.procedures p
JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE m.definition LIKE '%@p_TEXT = @temp;%'
"@

foreach ($proc in $procs) {
    $procName = $proc.name
    $definition = $proc.definition

    # 替换目标语句，保留原有换行格式
    $alterDefinition = $definition -replace 'CREATE PROCEDURE', 'ALTER PROCEDURE'
    $alterDefinition = $alterDefinition -replace '@p_TEXT = @temp;', '@p_TEXT = @temp;commit;'

    # 执行ALTER语句
    $alterScript = "$alterDefinition`nGO"
    Write-Host "Processing procedure: $procName"
    Invoke-SqlCmd -ServerInstance $serverName -Database $dbName -Query $alterScript
}

优势：保留代码原有换行，避免单行拼接引发的注释问题；原生支持超长文本，无长度限制；可通过正则表达式进一步优化匹配规则，精准排除字符串常量、注释中的目标语句。

注意事项 #

1. 执行修改前务必备份所有存储过程定义，防止意外错误。
2. 先在非生产环境验证生成的ALTER脚本逻辑正确性。
3. 若存储过程中存在@p_TEXT = @temp;出现在字符串常量或注释中的情况，需细化匹配规则避免误修改。

内容的提问来源于stack exchange，提问作者gordon613