萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL 腳本檔案合併自動化:T-SQL 預存程序與 CLR C# 兩種實作方案

需求背景

專案部署時需將多個 .sql 腳本檔按順序合併為單一發布腳本。手動複製貼上易出錯且效率低,本文提供兩種自動化方案。

方案一:T-SQL 預存程序 sp_ScriptMerge (利用 xp_cmdshell + OLE Automation)

前置設定

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;

核心邏輯

  1. 掃描目錄 @Path 下所有 .sql 檔 (或依 @FilesList 指定順序)。
  2. 逐檔呼叫 xp_cmdshell 'type "檔案路徑"' 讀取內容。
  3. 寫入合併檔,檔首/檔尾加入標記註解 (含時間戳、檔名、序號)。
  4. 使用 sp_OACreate 'Scripting.FileSystemObject' 建立檔案寫入。

服務啟用

呼叫範例

EXEC master.dbo.sp_ScriptMerge
    @Path = N'C:\Deploy\Scripts',
    @FilesList = N'01_CreateTables.sql,02_InsertSeedData.sql,03_CreateIndexes.sql',
    @NewFileName = N'Deploy_20240115.sql';

方案二:CLR 預存程序 (C# 實作、效能更佳、無 xp_cmdshell 風險)

C# 核心代碼

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;

public class ClScriptMerge
{
    [SqlProcedure]
    public static void SQLScriptMerge(string Path, string FilesList, string NewFileName)
    {
        try
        {
            var dir = new DirectoryInfo(Path);
            var files = string.IsNullOrEmpty(FilesList)
                ? dir.GetFiles("*.sql")
                : FilesList.Split(',').Select(f => new FileInfo(Path + "\\" + f.Trim())).ToArray();

            string scriptNr = "Nr" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
            if (string.IsNullOrEmpty(NewFileName))
                NewFileName = "合併腳本" + DateTime.Now.ToString("yyyyMMdd") + ".sql";

            string outPath = Path + "\\" + NewFileName;
            SqlContext.Pipe.Send("輸出檔案: " + outPath);

            using (var sw = new StreamWriter(outPath, false, Encoding.Unicode))
            {
                sw.WriteLine($"Go\n/*============ {scriptNr}====Start===================================*/\nGo\n");
                int count = 0;
                foreach (var f in files.Where(f => f.Name != NewFileName))
                {
                    count++;
                    string content = File.ReadAllText(f.FullName, Encoding.Default); // 或偵測編碼
                    sw.WriteLine($"Go\n/* {scriptNr} ({count}): {f.Name} */\nGo\n");
                    sw.WriteLine(content);
                }
                sw.WriteLine($"Go\n/* {scriptNr} 合併完成 (共 {count} 個腳本) */\nGo\n");
            }
            SqlContext.Pipe.Send("合併完成,共 " + count + " 個檔案。");
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("錯誤: " + ex.Message);
        }
    }
}

部署 CLR 組件

-- 1. 啟用 CLR
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

-- 2. 建立非對稱金鑰/憑證簽署組件 (生產環境建議)
-- CREATE ASYMMETRIC KEY ... FROM EXECUTABLE FILE = '...dll';
-- CREATE LOGIN ... FROM ASYMMETRIC KEY ...; GRANT EXTERNAL ACCESS ASSEMBLY TO ...;

-- 3. 建立組件 (EXTERNAL_ACCESS 權限)
CREATE ASSEMBLY ClScriptMerge FROM 'C:\Path\ClScriptMerge.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;

-- 4. 建立預存程序包裝
CREATE PROC usp_CLScriptMerge
    @Path NVARCHAR(1024), @FilesList NVARCHAR(MAX), @NewFileName NVARCHAR(1024)
AS EXTERNAL NAME ClScriptMerge.[ClScriptMerge].SQLScriptMerge;

方案比較

維度T-SQL + xp_cmdshellCLR C#
部署複雜度低 (純 T-SQL)中 (需編譯 DLL、註冊組件)
安全性低 (需開啟 xp_cmdshell/OLE)高 (CLR 沙箱、可簽署)
效能低 (每檔啟動 cmd.exe)高 (進程內直接 IO)
編碼處理依賴系統預設碼頁可指定 Encoding.UTF8/Unicode
跨平台否是 (Linux SQL Server 支援 CLR CoreCLR)

建議

  • 臨時/測試環境、不想編譯 → T-SQL 方案。
  • 生產環境、CI/CD 流程、跨平台 → CLR 方案 (或改用 PowerShell/外部建構工具)。
copyright © 萬盛學電腦網 all rights reserved