三種方案對比
| 方案 | 原理 | 優點 | 缺點 | 適用場景 |
| 1. 動態 SQL 拼接 | 查 SYOBJECTS 拼 TRUNCATE TABLE 字符串執行 | 代碼簡短 | 字符串長度限制(8000/MAX),表多時截斷失敗 | 表較少(<50)的臨時清理 |
| 2. 游標循環 | 游標遍歷表名,逐個 EXEC('TRUNCATE TABLE ' + @Name) | 無長度限制,可加過濾邏輯,可做存儲過程復用 | 逐條執行較慢,需處理外鍵依賴順序 | 常規生產清理、需選擇性清空 |
3. 非公開 SP SP_MSFOREACHTABLE | 內部游標封裝,單行 EXEC SP_MSFOREACHTABLE 'TRUNCATE TABLE ?' | 最簡潔,微軟內部工具 | 非文檔化、未來版本可能移除/行為變更 | 臨時測試、DBA 緊急操作 |
方案二完整腳本(可封裝為存儲過程)
CREATE PROC USP_Truncate_All_Tables @SchemaName SYSNAME = 'DBO', @ExcludeList VARCHAR(MAX) = ''
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL += N'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(O.SCHEMA_ID)) + '.' + QUOTENAME(O.NAME) + ';'
FROM SYS.OBJECTS O
WHERE O.TYPE = 'U'
AND SCHEMA_NAME(O.SCHEMA_ID) = @SchemaName
AND ',' + @ExcludeList + ',' NOT LIKE '%,' + O.NAME + ',%'; -- 排除列表
IF @SQL = '' PRINT '無符合條件的表';
ELSE EXEC SP_EXECUTESQL @SQL;
END;
關鍵注意事項
- 外鍵阻礙:
TRUNCATE 要求表無被引用的外鍵。需先 ALTER TABLE ... NOCHECK CONSTRAINT ALL 禁用約束,清理後 CHECK CONSTRAINT ALL 重建,或按拓撲序刪除/重建外鍵。
- 標識列重置:
TRUNCATE 自動將標識列種子重置為定義值(通常 1),DELETE 不會重置。
- 權限:需
ALTER 表權限,DB_DDLADMIN 角色即可。
- 事務日志:
TRUNCATE 只記錄頁釋放,日志極少;DELETE 逐行記錄,大表日志爆漲。