合并SQL脚本文件

概述

---

    在平时的工作中，我会经常的碰到这样需要合并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
Go
if object_id('sp_ScriptMerge') Is Not Null
    Drop proc sp_ScriptMerge
Go    
Create Proc sp_ScriptMerge
(
    @Path nvarchar(1024),
    @FilesList nvarchar(max)= null,
    @NewFileName nvarchar(1024)=null
)
As
/*合并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
Go

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

 

Exec sys.sp_configure @configname = 'show advanced options',@configvalue = 1
reconfigure
go
Exec sys.sp_configure @configname = 'xp_cmdshell',@configvalue = 1
reconfigure
Go
Exec sys.sp_configure @configname = 'Ole Automation Procedures',@configvalue = 1
reconfigure
Go

测试：

use master
GO
Exec master.dbo.sp_ScriptMerge
        @Path = 'C:\Users\Administrator\Desktop\temp', -- nvarchar(1024)
        @FilesList = '', -- nvarchar(max)
        @NewFileName = '合并脚本20110905.sql' -- nvarchar(1024)
 

 

 

*********** 合并脚本完成 ***********
合并后脚本文件:  C:\Users\Administrator\Desktop\temp\合并脚本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(@"Go\n/*============ " + strScriptNr + "====Start===================================*/\nGo\n"); //记录生成的合并脚本编号&合并动作的开始位置

            foreach (string strFile in strFiles)
            {
                

                if (strFile !=NewFileName)
                {
                    intCount += 1;
                    SW.WriteLine(@"/* " + strScriptNr +@" ("+intCount+@"):  " + strFile + "*/\nGo\n"); //记录合并哪一个脚本文件
                    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 + " 个文件)===================================*/\nGo\n"); //记录生成的合并脚本文件个数&合并动作的结束位置
            SW.Close();

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


        }
        catch (System.Exception e)
        {
            SqlContext.Pipe.Send("\n在方法SQLScriptMerge内发生错误： \n\n" + e.ToString());
        }
    }

}

存储过程代码：

Use master
GO
--启动CLR
Exec sp_configure 'clr enable',1
Go
Reconfigure
GO
--先设置数据库选项
Alter Database Master Set TRUSTWORTHY On

Go

--存储过程
If object_id('sp_ScriptMerge2') Is Not Null
    Drop Proc sp_ScriptMerge2
Go

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


Go
Create Assembly ScriptMerge
    From 'E:\Test\Objects\ISTest\ScriptMerge\ScriptMerge\bin\Debug\ScriptMerge.dll'
    
create proc sp_ScriptMerge2
(
    @Path nvarchar(1024),
    @FilesList nvarchar(max),
    @NewFileName nvarchar(1024)
)
As External Name ScriptMerge.clScriptMerge.SQLScriptMerge
Go
    

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