專案部署時需將多個 .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;
@Path 下所有 .sql 檔 (或依 @FilesList 指定順序)。xp_cmdshell 'type "檔案路徑"' 讀取內容。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';
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);
}
}
}
-- 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_cmdshell | CLR C# |
|---|---|---|
| 部署複雜度 | 低 (純 T-SQL) | 中 (需編譯 DLL、註冊組件) |
| 安全性 | 低 (需開啟 xp_cmdshell/OLE) | 高 (CLR 沙箱、可簽署) |
| 效能 | 低 (每檔啟動 cmd.exe) | 高 (進程內直接 IO) |
| 編碼處理 | 依賴系統預設碼頁 | 可指定 Encoding.UTF8/Unicode |
| 跨平台 | 否 | 是 (Linux SQL Server 支援 CLR CoreCLR) |