萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 一鍵清空所有表數據並重置標識列種子

三種方案對比

方案原理優點缺點適用場景
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 逐行記錄,大表日志爆漲。
copyright © 萬盛學電腦網 all rights reserved