您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

T-SQL 游標批量刪除指定前綴資料表完整腳本

編輯：關於SqlServer

當資料庫累積大量測試表或需依命名規則清理舊表（如 PUB_*、TMP_*），逐一手動 DROP TABLE 不僅繁瑣且易遺漏。利用游標動態建構並執行刪除陳述式，可一次完成批量清理。

完整刪除腳本

DECLARE @tablename NVARCHAR(128),
        @sql       NVARCHAR(MAX);

-- 宣告唯讀順向游標，效能較佳
DECLARE cur_delete_table CURSOR 
    LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
    SELECT QUOTENAME(name)  -- 自動處理特殊字元與保留字
    FROM sys.tables         -- 建議用 sys.tables 取代舊版 sysobjects
    WHERE name LIKE N'PUB[_]%'  -- 以 PUB_ 開頭的表
      AND type = 'U';           -- 僅使用者表

OPEN cur_delete_table;
FETCH NEXT FROM cur_delete_table INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'DROP TABLE ' + @tablename;
    PRINT N'執行: ' + @sql;   -- 列印方便稽核
    EXEC sp_executesql @sql;  -- 參數化執行，防注入
    
    FETCH NEXT FROM cur_delete_table INTO @tablename;
END

CLOSE cur_delete_table;
DEALLOCATE cur_delete_table;

關鍵優化說明

優化點	原寫法	改良版	效益
系統表	sysobjects	sys.tables	標準目錄檢視，相容性佳
物件名稱	直接串接	QUOTENAME()	防止特殊字元/保留字語法錯誤
游標選項	預設	LOCAL STATIC READ_ONLY FORWARD_ONLY	減少鎖定、提升效能、避免平行計畫
動態執行	EXEC (@sql)	sp_executesql	計畫重用、參數化、安全性較高
變數長度	VARCHAR(30/500)	NVARCHAR(128/MAX)	支援 Unicode、長物件名、長 SQL

執行前務必確認

備份資料庫：操作不可逆，先備份或快照
測試 LIKE 條件：先跑 SELECT name FROM sys.tables WHERE name LIKE N'PUB[_]%' 確認目標清單
檢查外鍵相依：若表被參考，需先刪除 FK 或用 DROP TABLE ... CASCADE（SQL Server 2016+）
權限：需 ALTER 或 CONTROL 權限於目標結構描述

替代方案：無游標集合式寫法

若表數量極大（數千以上），游標逐筆執行較慢，可改用動態 SQL 批次產生：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'DROP TABLE ' + QUOTENAME(name) + N';'
FROM sys.tables
WHERE name LIKE N'PUB[_]%' AND type = 'U';

PRINT @sql;
-- EXEC sp_executesql @sql;  -- 確認無誤後取消註解執行

此法單次批次提交，網路往返少，大量刪除效能顯著優於游標。