无需使用游标清空指定列表中的SQL Server数据表

我会将临时数据从外部源上传至SQL Server空表，但每次涉及的表并不固定。在MERGE存储过程执行完成后，需要清空这些表。这些表规模不大，单次填充的数据量也不多，目前仅能通过游标实现该操作，现有代码可正常运行，但想找到更优方案。

原游标实现代码 #

CREATE TABLE #Temp_TBL (name NVARCHAR(max));

INSERT INTO #Temp_TBL (name) 
SELECT 
    t.name 
FROM 
    SYS.DM_DB_PARTITION_STATS s 
INNER JOIN 
    sys.tables t ON t.[object_id] = s.[object_id] 
WHERE 
    s.row_count != 0 
    AND t.type_desc = 'USER_TABLE'

DECLARE @tableName nvarchar(max);

DECLARE cur CURSOR LOCAL FOR
    SELECT name FROM #Temp_TBL

OPEN cur

FETCH NEXT FROM cur INTO @tableName

WHILE @@FETCH_STATUS = 0 
BEGIN
    EXEC('TRUNCATE TABLE '+ @tableName); 
    FETCH NEXT FROM cur INTO @tableName
END

CLOSE cur
DEALLOCATE cur

DROP TABLE #Temp_TBL

无游标优化方案 #

针对你的场景，完全可以摒弃游标，通过动态SQL批量生成并执行TRUNCATE语句，代码更简洁且性能更优。

优化后代码 #

DECLARE @sql NVARCHAR(MAX) = ''

-- 批量生成TRUNCATE语句，用QUOTENAME处理表名避免语法错误和注入风险
SELECT @sql += 'TRUNCATE TABLE ' + QUOTENAME(t.name) + ';'
FROM SYS.DM_DB_PARTITION_STATS s
INNER JOIN sys.tables t ON t.[object_id] = s.[object_id]
WHERE s.row_count != 0
  AND t.type_desc = 'USER_TABLE'

-- 仅当生成了有效SQL时才执行
IF @sql <> ''
    EXEC sp_executesql @sql

核心改进点 #

• 消除游标开销：游标是逐行循环处理，批量动态SQL一次性生成所有执行语句并执行，减少了循环和游标管理的额外开销。
• 提升安全性：使用QUOTENAME函数给表名添加方括号，避免表名包含特殊字符、关键字时出现语法错误，同时防范SQL注入风险。
• 简化逻辑：无需创建临时表存储表名，直接从系统视图筛选目标表并生成执行语句，减少中间步骤。

自定义筛选扩展 #

如果需要更精准地定位要清空的表（比如特定schema、特定命名规则的表），可以在WHERE条件中添加筛选：

-- 示例：仅清空dbo schema下的表
AND t.schema_id = SCHEMA_ID('dbo')

-- 示例：仅清空以Temp_开头的表
AND t.name LIKE 'Temp_%'

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