合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下蛋运作通过。

 

相关文章