依據 SQL 腳本自動升級資料庫結構：三種解決方案比較與實作

專案背景

客戶舊版資料庫已上線且含生產資料，新版本需修改結構（新增表、欄位、修改型別、存儲過程等），需開發工具自動將舊庫升級為新結構並保留資料。

方案一：整庫腳本重建 + 資料遷移（簡單但停機久）

1. SSMS 產生新資料庫完整腳本（結構+資料）。
2. 舊庫改名，執行新腳本建立新庫。
3. 用「匯入/匯出精靈」或 INSERT SELECT 將舊資料導入新表。

缺點：大量資料遷移耗時長，客戶端操作複雜。

方案二：表級重建 + 資料就地搬移（較細緻）

核心步驟

1. 重命名舊表：EXEC sp_rename 'OldTable', 'OldTable_Bak'
2. 執行新表建立腳本。
3. 將資料從備份表複製回新表：
   INSERT INTO NewTable(col1,col2...) SELECT col1,col2... FROM OldTable_Bak
4. 刪除舊存儲過程/約束，重建新版本。

輔助 SQL

-- 重命名資料庫
EXEC sp_renamedb 'OldDB', 'NewDB'
-- 重命名表/欄位
EXEC sp_rename 'OldTable', 'NewTable'
EXEC sp_rename 'Table.OldCol', 'NewCol', 'COLUMN'
-- 複製結構
CREATE TABLE NewTable SELECT * FROM OldTable WHERE 1=2
-- 複製資料
INSERT INTO NewTable SELECT * FROM OldTable
-- 刪除約束
ALTER TABLE T DROP CONSTRAINT PK_Col
-- 刪除程序
DROP PROCEDURE dbo.ProcName

方案三：就地 ALTER TABLE 同步結構（零資料搬移、最快）

邏輯流程

1. 讀取舊表結構：SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'T'
2. 比對新腳本定義的結構：

• 欄位存在但型別/可空性不同 → ALTER TABLE T ALTER COLUMN Col NewType NOT NULL
• 欄位不存在 → ALTER TABLE T ADD Col NewType NOT NULL
• （進階）多餘欄位 → ALTER TABLE T DROP COLUMN Col

技術要點

• 使用 ADO.NET 執行動態 SQL，腳本分行以 \n 分隔。
• 正則表達式解析腳本中的 CREATE TABLE、ALTER TABLE、CREATE PROCEDURE 區塊。
• 交易包裹整個升級過程，失敗自動回滾。

方案比較與選擇建議

本專案最終採用方案三核心邏輯，搭配方案二的約束/程序重建機制，開發出自動化升級工具，已成功應用於多位客戶現場升級。