SQL Server 批量刪除特定前綴資料表的實作指南

前言

在資料庫維護作業中，有時需要一次刪除多張符合特定命名規則的資料表。例如開發環境中以 "test" 為前綴的臨時表、測試用表等。SQL Server 沒有直接的「批量 DROP TABLE」指令，但我們可以透過動態 SQL 結合系統目錄檢視來達成此需求。

核心邏輯與步驟

1. 宣告變數儲存資料表名稱篩選條件（如 @tableName = 'test'）。
2. 查詢 sys.tables 取得符合 LIKE '%' + @tableName + '%' 的所有資料表名稱。
3. 使用 FOR XML PATH('') 將多行名稱串聯成逗號分隔字串，並用 STUFF 移除首字逗號。
4. 組合成 DROP TABLE table1, table2, ... 的動態 SQL 字串。
5. 呼叫 EXEC(@sql) 執行批量刪除。

完整範例碼

/*--------------------------------
功能說明: 批量 Drop Table
使用說明: 執行前請務必確認 WHERE 條件精確匹配欲刪除的資料表，
          避免誤刪重要資料表。
---------------------------------*/
-- 參數定義
DECLARE @tableName AS NVARCHAR(50) = N'test';  -- 查詢條件，建議盡量精確
DECLARE @tableNames AS NVARCHAR(3000);
DECLARE @sql AS NVARCHAR(3000);

-- 防呆：若參數為空則設為不存在的名稱，避免全庫掃描
IF @tableName = '' SET @tableName = N'tableName';

-- 取得符合條件的資料表清單
SET @tableNames = (
    SELECT ',' + name
    FROM sys.tables
    WHERE name LIKE '%' + @tableName + '%'
    FOR XML PATH('')
);

-- 移除開頭逗號
SET @tableNames = STUFF(@tableNames, 1, 1, '');

-- 組合並執行動態 SQL
IF @tableNames <> ''
BEGIN
    SET @sql = N'DROP TABLE ' + @tableNames;
    PRINT @sql;  -- 方便除錯確認
    EXEC(@sql);
END
ELSE
BEGIN
    PRINT N'找無符合條件的資料表，未執行刪除。';
END

注意事項與最佳實務

• 嚴格驗證條件：LIKE 條件過寬會導致誤刪，建議先用 SELECT name FROM sys.tables WHERE name LIKE ... 預覽清單。
• 權限需求：執行者需具備目標資料表的 DROP 權限（通常為 db_ddladmin 或 db_owner 角色）。
• 外鍵相依：若資料表被外鍵參考，直接 DROP 會失敗，需先移除相依關係或使用 DROP TABLE ... CASCADE（SQL Server 2016+ 支援）。
• 交易控管：大量刪除建議包裹在交易中，並在執行前備份資料庫。

進階擴充：支援多 Schema

若資料表分屬不同 Schema，可修改查詢加入 SCHEMA_NAME(schema_id)，組合成 schema.table 格式：

SELECT ',' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.tables
WHERE name LIKE '%' + @tableName + '%'
FOR XML PATH('')

QUOTENAME 可自動處理特殊字元與保留字，提升安全性。