SQL Server 2008 批量在多個數據庫創建同一存儲過程的自動化方案

需求背景

十幾個數據庫結構相同，需部署同一存儲過程，手動逐個執行效率低且易出錯。

方案一：T-SQL 動態 SQL 批量執行（推薦）

DECLARE @sp_name NVARCHAR(128) = 'usp_MyProc';
DECLARE @sp_def NVARCHAR(MAX) = N'
CREATE PROCEDURE usp_MyProc
AS
BEGIN
    -- 過程體
    SELECT 1;
END';

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'USE [' + name + N']; ' + @sp_def + N' GO; '
FROM sys.databases
WHERE name LIKE 'BizDB_%'  -- 篩選目標庫
  AND state = 0;           -- 僅在線數據庫

EXEC sp_executesql @sql;

方案二：PowerShell 腳本自動化

$databases = @('DB1','DB2','DB3')
$script = Get-Content 'CreateProc.sql' -Raw

foreach ($db in $databases) {
    Invoke-Sqlcmd -ServerInstance 'localhost' -Database $db -Query $script
    Write-Host "Deployed to $db"
}

方案三：C# / Python 程式化部署

• 讀取連接字串配置（如 appsettings.json）。
• 遍歷連接池，執行 CREATE PROCEDURE 腳本。
• 記錄成功/失敗日誌，支援重試與回滾。

最佳實踐提醒

1. 版本控制：存儲過程腳本納入 Git，標註版本號。
2. 冪等性：腳本開頭加 IF EXISTS ... DROP PROCEDURE，保證可重複執行。
3. 權限最小化：部署賬號僅擁有 CREATE PROCEDURE 權限。
4. 事務保護：單庫部署包裝在事務中，失敗自動回滾。

採用動態 SQL 或腳本自動化，即可一次性完成多庫存儲過程部署，提升運維效率。