SQL Server 資料庫升級腳本實戰：從 1.0 到 1.1 的結構遷移與預存程序重建全流程

專案背景：遠端指導客戶升級資料庫版本

客戶端環境無法直接遠端連線，僅能透過電話指導在 SSMS 執行升級腳本。版本差異：
✅ CT_OuterCard 表新增 6 個欄位
✅ 新增大量預存程序
✅ 其他表結構、資料無變動

階段一：產生 1.1 版預存程序腳本（來源端）

1. SSMS 物件總管 → 右鍵資料庫 → 工作 → 產生指令碼。
2. 選擇「選取特定資料庫物件」→ 勾選 預存程序（若有函數亦勾選）。
3. 輸出選項：儲存至新查詢視窗（方便複製）。
4. 其餘選項保持預設（含 IF NOT EXISTS、DROP 等）。

執行完成後，查詢視窗即包含所有預存程序的 CREATE PROCEDURE 完整腳本。

階段二：撰寫升級主腳本（目標端執行）

/* ============================================
   GPOS 1.0 → 1.1 升級腳本  2013-07-04
   ============================================ */
USE [GPOSDB];
GO

/* ---- 1. 刪除所有現有預存程序 ---- */
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'DROP PROCEDURE ' + QUOTENAME(name) + N'; '
FROM sys.procedures;
EXEC sp_executesql @sql;
GO

/* ---- 2. 為 CT_OuterCard 新增 6 個欄位 ---- */
ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransCurrCount] INT NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitTransCurrCount] DEFAULT ((0));
ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransType] INT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitTransType] DEFAULT ((0));
ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransTotal] DECIMAL(18,2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransTotal] DEFAULT ((0));
ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransCurrTotal] DECIMAL(18,2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransCurrTotal] DEFAULT ((0));
ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitCarNo] INT NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitCarNo] DEFAULT ((0));
ALTER TABLE [dbo].[CT_OuterCard] ADD [D_LimitDate] DATETIME NOT NULL CONSTRAINT [DF_CT_OuterCard_D_LimitDate] DEFAULT (GETDATE());
GO

/* ---- 3. 部署 1.1 版所有預存程序（貼上階段一產生的腳本） ---- */
USE [GPOSDB];
GO
/****** 物件: StoredProcedure [dbo].[Report_GreaserSaleStat] ******/
SET ANSI_NULLS OFF; GO
SET QUOTED_IDENTIFIER OFF; GO

CREATE PROC [dbo].[Report_GreaserSaleStat]
    @StartDate DATETIME,
    @EndDate   DATETIME,
    @Action    INT  -- 0:交易記錄 1:班次記錄
AS
BEGIN
    /* ... 此處省略完整程序碼，實際部署請貼上完整內容 ... */
END
GO

-- 其他預存程序依序貼上...

關鍵技術細節

• 動態 SQL 批次刪除程序：避免逐一手寫 DROP，適用於大量物件清理。
• ALTER TABLE ... ADD ... CONSTRAINT ... DEFAULT：單陳述式完成加欄位、加預設值、加約束命名，確保現有資料自動填入預設值，不阻塞應用。
• 腳本分區管理：以註解區塊明確區分「刪除舊物件 → 結構變更 → 部署新物件」，降低執行風險。
• 版本控制：腳本首行標註版本、日期、作者，納入版控系統。

執行與驗證流程

1. 備份客戶端資料庫（務必）。
2. 在 SSMS 開啟新查詢，貼上完整升級腳本，按 F5 執行。
3. 查看「訊息」頁籤：確認無紅字錯誤，PRINT 訊息顯示各階段完成。
4. 物件總管重新整理：驗證 CT_OuterCard 新欄位存在、預存程序數量與 1.1 版一致。
5. 執行應用程式煙霧測試：核心功能（開單、查詢、報表）運作正常。

擴充：若主鍵/索引亦需變更

-- 範例：修改主鍵
ALTER TABLE dbo.SomeTable DROP CONSTRAINT PK_SomeTable;
ALTER TABLE dbo.SomeTable ADD CONSTRAINT PK_SomeTable PRIMARY KEY (NewCol1, NewCol2);

原則：先刪除約束 → 再修改欄位/加欄位 → 最後重建約束/索引，避免相依衝突。

結語

這套「產生新版程式碼 → 動態清理舊物件 → 結構增量變更 → 部署新物件」的升級腳本模式，已在多個專案驗證可行。核心在於腳本冪等性（可重複執行不報錯）與清晰的階段劃分。下次面對資料庫版本升級，請依此範本調整，即可從容應對遠端指導、自動化部署等場景。