SQL Server如何统计EXEC动态SQL实际成功修改的表数量？

原脚本存在3个核心问题，无法满足需求：

• 变量赋值逻辑错误：@ExpectedCounter初始值设为0后未重新赋值，和待修改数量永远无法匹配
• 未使用显式事务：SQL Server中DDL语句默认自动提交，已经执行成功的修改无法回滚
• 批量拼接执行缺陷：所有ALTER语句合并为单个批处理执行时，任意一条语句失败会直接终止整个批处理，无法统计实际成功条数，也无法定位失败位置

可通过「逐语句执行+异常捕获+显式事务」的方式实现需求，完整脚本如下：

USE FXecute
GO
SET NOCOUNT ON;
SET XACT_ABORT ON; -- 遇到严重错误时自动回滚当前事务，避免孤立事务遗留

DECLARE @ExpectedCounter INT = 0, 
        @ActualSuccessCounter INT = 0,
        @CurrentCmd VARCHAR(MAX),
        @CurrentOrder INT = 1,
        @TotalCmd INT = 0,
        @ErrorMsg NVARCHAR(MAX);

-- 生成待执行修改列表
DROP TABLE IF EXISTS #List
CREATE TABLE #List (
    Command varchar(max), 
    SchemaName SYSNAME,
    TableName SYSNAME,
    ColumnName SYSNAME,
    OrderBy INT IDENTITY(1,1)
)
    
INSERT INTO #List (Command, SchemaName, TableName, ColumnName)
 SELECT  
'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] DECIMAL(22,6)' AS Command,
TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM FXecute.INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'DECIMAL' 
AND (column_name  LIKE  '%amount%' or column_name  LIKE  '%amt%' OR column_name  LIKE  '%total%' OR column_name  LIKE  '%USD%') 
AND TABLE_NAME NOT LIKE 'syncobj%'

-- 赋值预期执行数：默认按列修改条数统计
SET @ExpectedCounter = @@ROWCOUNT;
SET @TotalCmd = @ExpectedCounter;
PRINT '预期修改列数：' + CAST(@ExpectedCounter AS NVARCHAR(10));
-- 如果需要按表数量统计，放开下面两行注释
-- SELECT @ExpectedCounter = COUNT(DISTINCT SchemaName+'.'+TableName) FROM #List
-- PRINT '预期修改表数：' + CAST(@ExpectedCounter AS NVARCHAR(10));

-- 开启显式事务
BEGIN TRANSACTION;

WHILE @CurrentOrder <= @TotalCmd
BEGIN
    SELECT @CurrentCmd = Command FROM #List WHERE OrderBy = @CurrentOrder;
    BEGIN TRY
        EXEC(@CurrentCmd);
        -- 语句执行成功，成功计数+1
        SET @ActualSuccessCounter = @ActualSuccessCounter + 1;
        PRINT '执行成功：' + @CurrentCmd;
    END TRY
    BEGIN CATCH
        -- 捕获错误信息
        SET @ErrorMsg = N'执行失败，语句：' + @CurrentCmd + CHAR(13) + N'错误信息：' + ERROR_MESSAGE();
        PRINT @ErrorMsg;
        -- 出错后直接终止循环
        BREAK;
    END CATCH

    SET @CurrentOrder = @CurrentOrder + 1;
END

-- 结果比对与事务处理
IF @ActualSuccessCounter = @ExpectedCounter AND @ErrorMsg IS NULL
BEGIN
    PRINT '所有修改执行成功，提交事务';
    COMMIT TRANSACTION;
END
ELSE
BEGIN
    PRINT '执行未达到预期，回滚所有修改；实际成功条数：' + CAST(@ActualSuccessCounter AS NVARCHAR(10)) + '，预期条数：' + CAST(@ExpectedCounter AS NVARCHAR(10));
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END

DROP TABLE IF EXISTS #List;
GO

关键说明 #

• 如果需要按修改的表数量而非列修改次数做比对，除了调整预期计数为去重表数外，还需要在执行完成后统计实际成功执行过的去重表数量，替换比对逻辑中的@ActualSuccessCounter即可，避免同一张表多个列修改时计数重复
• SET XACT_ABORT ON必须开启，避免出现运行时错误导致事务无法正常提交/回滚
• 逐行循环执行虽然比批量拼接执行稍慢，但可以精准定位失败语句，也能准确统计成功数量，是满足校验回滚需求的最稳妥方式
• ALTER COLUMN操作会持有对应表的架构修改锁，执行期间会阻塞其他会话对目标表的访问，建议在业务低峰期运行

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