合并SQL脚本文件的方法分享_mssql数据库教程-查字典教程网

概述

--------------------------------------------------------------------------------

在平时的工作中，我会经常的碰到这样需要合并SQL脚本的问题。如，有很多的SQL脚本文件，需要按照一定的先后顺序，再生成一个合并SQL脚本文件，然后再发布到用户SQL Server服务器上。

合并SQL脚本文件，最直接的方法就是新建1个空白的SQL脚本文件，再把需要合并的SQL脚本文件内容复制到新建的SQL文件中。当初，我合并脚本的操作方法与刚说的有类似。我在Microsoft SQL Server Management Studio(MSSMS)新建一个查询，再把要合并的SQL脚本文件，一个一个的打开，并复制内容到新建查询中，然后生成合并脚本文件。

上面的方法，对于几个SQL脚本文件合并来说，似乎没什么问题。但是，当我们要合并的脚本很多，一文件一个文件的内容复制，是比较繁琐的事情，要是能有1个简单合并脚本工具就最好不过了。下面介绍我写的两个合并SQL脚本文件的存储过程.

第1个存储过程，只要调用sp_OACreate实现合并SQL脚本文件。

复制代码代码如下:

use master

if object_id('sp_ScriptMerge') Is Not Null

Drop proc sp_ScriptMerge

Create Proc sp_ScriptMerge

(

@Path nvarchar(1024),

@FilesList nvarchar(max)= null,

@NewFileName nvarchar(1024)=null

)

/*合并SQL脚本文件(SQL)V1.0 Andy 2011-9-1*/

Declare

@ScriptNr nchar(21),

@subdirectoryStr nvarchar(512),

@Dir nvarchar(1024),

@ScriptCount int

Declare @subdirectoryTB Table (subdirectory nvarchar(512),depth smallint,[file] smallint)

Declare @tmp table(row smallint identity primary key,fileName nvarchar(512))

Set Nocount on

if right(@Path,1)<>'' Set @Path=@Path+''

If Isnull(@NewFileName,'')='' Set @NewFileName=N'合并脚本-'+Convert(nvarchar(8),getdate(),112)

if lower(right(@NewFileName,4))<>'.sql' Set @NewFileName=@NewFileName+'.sql'

Set @NewFileName=@Path+@NewFileName

Set @ScriptNr='Nr: '+Replace(replace(Replace(replace(convert(nvarchar(23),getdate(),121),'-',''),':',''),' ',''),'.','')

Set @ScriptCount=0

/*读取脚本文件内容*/

if @FilesList >''

Begin

Set @FilesList='Select N'''+replace(@FilesList,',',''' Union All Select N''')+''''

Insert into @tmp([fileName]) Exec(@FilesList)

End

if object_id('Tempdb..#') Is Not Null Drop Table #

Create table #(row int identity(1,1) Primary key,text nvarchar(max))

Insert into @subdirectoryTB Exec xp_dirtree @Path,1,1

Declare cur_file cursor for

Select a.subdirectory

From @subdirectoryTB As a

left Join @tmp As b ON b.fileName=a.subdirectory

Where a.[file]=1 And a.subdirectory like '%.sql'

And (b.fileName=a.subdirectory Or Not Exists(Select 1 From @tmp))

Order By isnull(b.row,0),a.subdirectory

Open cur_file

fetch next From cur_file into @subdirectoryStr

While @@FETCH_STATUS = 0

Begin

Set @ScriptCount=@ScriptCount+1

Insert into #(text) Select +Char(13)+Char(10)+ N'Go'+Char(13)+Char(10)+ N'/* '+@ScriptNr+' ('+rtrim(@ScriptCount)+'): '+@subdirectoryStr+' */'+Char(13)+Char(10)+ N'Go'+Char(13)+Char(10)

Set @Dir='Type '+@Path+'"'+@subdirectoryStr+'"'

Insert into #(text)

Exec sys.xp_cmdshell @Dir

fetch next From cur_file into @subdirectoryStr

End

Close cur_file

Deallocate cur_file

if @ScriptCount >0 Insert into #(text) Select +Char(13)+Char(10)+ N'Go'+Char(13)+Char(10)+ N'/* '+@ScriptNr+' 合并完成(合计 '+rtrim(@ScriptCount)+' 各脚本文件). */'+Char(13)+Char(10)+ N'Go'+Char(13)+Char(10)

/*写入合并脚本文件*/

if @ScriptCount>0

Begin

Declare @object int,

@FileID int,

@hr int,

@src varchar(255),

@desc varchar(255),

@row int,

@text nvarchar(max)

Exec @hr=sp_OACreate 'Scripting.FileSystemObject',@object output

If @hr <> 0 Goto File_ErrorHandler

Exec @hr = sp_OAMethod @object,'CreateTextFile',@FileID OUTPUT, @NewFileName

If @hr <> 0 Goto File_ErrorHandler

Set @row=1

While Exists(Select 1 From # Where row=@row)

Begin

Set @text=(Select text From # Where row=@row)

Exec @hr = sp_OAMethod @FileID, 'WriteLine', NULL, @text

Set @row=@row +1

End

Goto File_Done

File_ErrorHandler:

Print N'*********** 读写文件的时候发生错误 ***********'

Exec @hr=sp_OAGetErrorInfo @object, @src OUT, @desc OUT

Select convert(varbinary(4),@hr) As hr, @src As Source, @desc As Description

File_Done:

Exec @hr = sp_OADestroy @FileID

Exec @hr = sp_OADestroy @object

Print N'*********** 合并脚本完成 ***********'

Print N'合并后脚本文件: '+@NewFileName

End

调用上面存储过程前，需要确认启用 OLE Automation Procedures和xp_cmdshell 选项：

复制代码代码如下:

调用上面存储过程前，需要确认启用 OLE Automation Procedures和xp_cmdshell 选项：

复制代码代码如下:

Exec sys.sp_configure @configname = 'show advanced options',@configvalue = 1

reconfigure

Exec sys.sp_configure @configname = 'xp_cmdshell',@configvalue = 1

reconfigure

Exec sys.sp_configure @configname = 'Ole Automation Procedures',@configvalue = 1

reconfigure

测试：

复制代码代码如下:

use master

Exec master.dbo.sp_ScriptMerge

@Path = 'C:UsersAdministratorDesktoptemp', -- nvarchar(1024)

@FilesList = '', -- nvarchar(max)

@NewFileName = '合并脚本20110905.sql' -- nvarchar(1024)

*********** 合并脚本完成 ***********合并后脚本文件: C:UsersAdministratorDesktoptemp合并脚本20110905.sql第2个是CLR存储过程，使用C#代码实现合并SQL脚本文件。

--------------------------------------------------------------------------------

C#代码：

复制代码代码如下:

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

using System.IO;

using System.Text;

public class clScriptMerge

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void SQLScriptMerge(string Path, string FilesList, string NewFileName)

{

try

{

string[] strFiles ;

FileInfo[] myFileInfo = (new DirectoryInfo(Path)).GetFiles("*.sql");

string strScriptNr = @"Nr" + DateTime.Now.ToString("yyyyMMddHHmmssFFF");

int intCount=0;

if (NewFileName == null || NewFileName=="")

{

NewFileName = "合并脚本" + DateTime.Now.ToString("yyyyMMdd") + ".sql";

}

SqlContext.Pipe.Send(NewFileName.ToString()); //打印已合并的SQL文件名

// 1.获得SQL脚本列表

if (FilesList != "")

{

strFiles = FilesList.Split(','); //筛分SQL脚本文件名列表,以","分隔

}

else

{

strFiles = new string[myFileInfo.Length];

for (int i = 0; i < myFileInfo.Length; i++)

{

strFiles[i] = myFileInfo[i].Name;

}

// 2.合并脚本

SqlContext.Pipe.Send("【SQL脚本文件列表】:n--------------------------------------------");

StreamWriter SW = new StreamWriter(Path + @"" + NewFileName, true, Encoding.Unicode); //使用Unicode编码

SW.WriteLine(@"Gon/*============ " + strScriptNr + "====Start===================================*/nGon"); //记录生成的合并脚本编号&合并动作的开始位置

foreach (string strFile in strFiles)

{

if (strFile !=NewFileName)

{

intCount += 1;

SW.WriteLine(@"/* " + strScriptNr +@" ("+intCount+@"): " + strFile + "*/nGon"); //记录合并哪一个脚本文件

using (StreamReader SR = new StreamReader(Path + @"" + strFile, Encoding.Default))

{

string line;

while ((line = SR.ReadLine()) != null)

{

SW.WriteLine(line);

}

SR.Close();

}

SqlContext.Pipe.Send(strFile.ToString()); //打印已合并的SQL文件名

}

SW.WriteLine(@"/*============ " + strScriptNr + "====End (一共 " + intCount + " 个文件)===================================*/nGon"); //记录生成的合并脚本文件个数&合并动作的结束位置

SW.Close();

SqlContext.Pipe.Send("nn【合成后文件】:n--------------------------------------------n" + NewFileName);

}

catch (System.Exception e)

{

SqlContext.Pipe.Send("n在方法SQLScriptMerge内发生错误： nn" + e.ToString());

}

存储过程代码：

复制代码代码如下:

Use master

--启动CLR

Exec sp_configure 'clr enable',1

Reconfigure

--先设置数据库选项

Alter Database Master Set TRUSTWORTHY On

--存储过程

If object_id('sp_ScriptMerge2') Is Not Null

Drop Proc sp_ScriptMerge2

If Exists(Select 1 From sys.assemblies Where Name=N'ScriptMerge')

Drop Assembly ScriptMerge

Create Assembly ScriptMerge

From 'E:TestObjectsISTestScriptMergeScriptMergebinDebugScriptMerge.dll'

create proc sp_ScriptMerge2

(

@Path nvarchar(1024),

@FilesList nvarchar(max),

@NewFileName nvarchar(1024)

)

As External Name ScriptMerge.clScriptMerge.SQLScriptMerge

以上的CLR存储过程代码是在SQL Server 2005 & Microsoft Visual Studio 2005下运行通过。