專案交付前清除測試資料、測試環境重置、資料遷移前準備空資料庫。需處理外鍵順序、Identity 種子重設、排除系統圖表表。
SET NOCOUNT ON;
-- 1. 忽略表清單
DECLARE @IgnoreTables TABLE (TableName SYSNAME);
INSERT INTO @IgnoreTables VALUES ('sysdiagrams');
-- 2. 收集所有 FK 資訊
DECLARE @AllFKs TABLE (
FKName SYSNAME, TableName SYSNAME, ColName SYSNAME,
RefTableName SYSNAME, RefColName SYSNAME, DeleteRule SYSNAME
);
INSERT INTO @AllFKs
SELECT f.name, OBJECT_NAME(f.parent_object_id), COL_NAME(fc.parent_object_id, fc.parent_column_id),
OBJECT_NAME(f.referenced_object_id), COL_NAME(fc.referenced_object_id, fc.referenced_column_id),
delete_referential_action_desc
FROM sys.foreign_keys f
JOIN sys.foreign_key_columns fc ON f.object_id = fc.constraint_object_id;
-- 3. 階段一:改為 CASCADE
DECLARE @Schema SYSNAME, @Table SYSNAME, @FK SYSNAME, @Col SYSNAME, @RefTable SYSNAME, @RefCol SYSNAME, @DelRule SYSNAME;
DECLARE curTables CURSOR LOCAL FAST_FORWARD FOR
SELECT SCHEMA_NAME(t.schema_id), t.name FROM sys.tables t;
OPEN curTables;
FETCH NEXT FROM curTables INTO @Schema, @Table;
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT 1 FROM @IgnoreTables WHERE TableName = @Table)
BEGIN
PRINT '['+@Schema+'].['+@Table+']';
DECLARE curFKs CURSOR LOCAL FAST_FORWARD FOR
SELECT FKName, ColName, RefTableName, RefColName FROM @AllFKs WHERE TableName = @Table;
OPEN curFKs;
FETCH NEXT FROM curFKs INTO @FK, @Col, @RefTable, @RefCol;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC(N'
ALTER TABLE ['+@Schema+'].['+@Table+'] DROP CONSTRAINT '+@FK+';
ALTER TABLE ['+@Schema+'].['+@Table+'] ADD CONSTRAINT '+@FK+'
FOREIGN KEY ('+@Col+') REFERENCES '+@RefTable+' ('+@RefCol+') ON DELETE CASCADE;');
PRINT ' ==> '+@FK+' set CASCADE';
END TRY
BEGIN CATCH
PRINT ' ==> FAIL '+@FK+': '+ERROR_MESSAGE();
END CATCH
FETCH NEXT FROM curFKs INTO @FK, @Col, @RefTable, @RefCol;
END
CLOSE curFKs; DEALLOCATE curFKs;
END
FETCH NEXT FROM curTables INTO @Schema, @Table;
END
CLOSE curTables; DEALLOCATE curTables;
-- 4. 階段二:刪除資料並重設種子
DECLARE curTables2 CURSOR LOCAL FAST_FORWARD FOR
SELECT SCHEMA_NAME(t.schema_id), t.name FROM sys.tables t;
OPEN curTables2;
FETCH NEXT FROM curTables2 INTO @Schema, @Table;
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT 1 FROM @IgnoreTables WHERE TableName = @Table)
BEGIN
PRINT 'Deleting ['+@Schema+'].['+@Table+']';
BEGIN TRY
EXEC(N'DELETE FROM ['+@Schema+'].['+@Table+']; DBCC CHECKIDENT (''['+@Table+']'', RESEED, 0);');
END TRY
BEGIN CATCH
PRINT ' ==> DELETE FAIL: '+ERROR_MESSAGE();
END CATCH
END
FETCH NEXT FROM curTables2 INTO @Schema, @Table;
END
CLOSE curTables2; DEALLOCATE curTables2;
-- 5. 階段三:還原 FK 規則
DECLARE curTables3 CURSOR LOCAL FAST_FORWARD FOR
SELECT SCHEMA_NAME(t.schema_id), t.name FROM sys.tables t;
OPEN curTables3;
FETCH NEXT FROM curTables3 INTO @Schema, @Table;
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT 1 FROM @IgnoreTables WHERE TableName = @Table)
BEGIN
DECLARE curFKs3 CURSOR LOCAL FAST_FORWARD FOR
SELECT FKName, ColName, RefTableName, RefColName, DeleteRule FROM @AllFKs WHERE TableName = @Table;
OPEN curFKs3;
FETCH NEXT FROM curFKs3 INTO @FK, @Col, @RefTable, @RefCol, @DelRule;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @newRule VARCHAR(20) = CASE @DelRule
WHEN 'NO_ACTION' THEN 'NO ACTION' WHEN 'CASCADE' THEN 'CASCADE'
WHEN 'SET_NULL' THEN 'SET NULL' WHEN 'SET_DEFAULT' THEN 'SET DEFAULT' END;
BEGIN TRY
EXEC(N'
ALTER TABLE ['+@Schema+'].['+@Table+'] DROP CONSTRAINT '+@FK+';
ALTER TABLE ['+@Schema+'].['+@Table+'] ADD CONSTRAINT '+@FK+'
FOREIGN KEY ('+@Col+') REFERENCES '+@RefTable+' ('+@RefCol+') ON DELETE '+@newRule+';');
PRINT ' ==> '+@FK+' restored to '+@newRule;
END TRY
BEGIN CATCH
PRINT ' ==> RESTORE FAIL '+@FK+': '+ERROR_MESSAGE();
END CATCH
FETCH NEXT FROM curFKs3 INTO @FK, @Col, @RefTable, @RefCol, @DelRule;
END
CLOSE curFKs3; DEALLOCATE curFKs3;
END
FETCH NEXT FROM curTables3 INTO @Schema, @Table;
END
CLOSE curTables3; DEALLOCATE curTables3;
PRINT 'All done.';
LOCAL FAST_FORWARD 游標:唯讀、順向、低開銷。DBCC CHECKIDENT (..., RESEED, 0):下一筆插入從 1 開始;若表為空且從未插入過,改用 RESEED, 0 後需插入一筆再刪除才生效,或直接 TRUNCATE TABLE(但 TRUNCATE 不觸發 Trigger、不記錄日誌、需無 FK 參考)。| 方法 | 優點 | 缺點 | 適用 |
|---|---|---|---|
| 本腳本 (DELETE+CASCADE) | 保留 Trigger、權限、統計資訊;可控 | 慢、記錄檔大、需暫改 FK | 中小型資料庫、需保留物件屬性 |
| TRUNCATE TABLE (逐表) | 極快、最少記錄檔、重設 Identity | 需先移除所有 FK、不觸發 Trigger、無法 WHERE | 大表、無 FK 或可暫時移除 FK |
| 卸離/刪除檔案/重建 | 最乾淨、檔案級重置 | 極高風險、需離線、權限複雜 | 極端情況、開發環境 |
| 重新部署 DACPAC/BACPAC | 結構+資料版本化、CI/CD 友善 | 需工具鏈、大資料庫部署慢 | DevOps 流程標準化 |