Overview
--------------------------------------------------------------------------------
In the usual work, I will often encounter this need to merge the SQL script problem. For example, there are a number of SQL script files that need to be regenerated to a merged SQL script file in a certain sequence, and then published to a user SQL Server server.
The most straightforward way to merge SQL script files is to create a new 1 blank SQL script file, and then copy the contents of the SQL script file that you want to merge into the new SQL file. At the outset, I merged the script with the same method as I just said. I create a new query in Microsoft SQL Server Management Studio (mssms), open the SQL script file that is to be merged one by one, copy the content to a new query, and then generate the merged script file.
The above method, for several SQL script file merges, seems to be no problem. However, when we want to merge a lot of scripts, a file copy of the contents of a file, it is more tedious things, if there are 1 simple combined scripting tools would be best. Below is a description of the two stored procedures that I have written to merge SQL script files.
A 1th stored procedure, as long as the sp_OACreate implementation merges the SQL script file.
Copy Code code as follows:
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
/* Merge SQL script file (SQL) V1.0 Andy 2011-9-1*/
Declare
@ScriptNr nchar (21),
@subdirectoryStr nvarchar (512),
@Dir nvarchar (1024),
@ScriptCount int
Declare @subdirectoryTB Table (subdirectory nvarchar (), 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 ' merge Script-' +convert (nvarchar (8), GETDATE (), 112)
If Lower (right (@NewFileName, 4)) <> '. sql ' Set @NewFileName = @NewFileName + '. sql '
Set @NewFileName = @Path + @NewFileName
Set @ScriptNr = ' Nr: ' +replace (replace (nvarchar (), GETDATE (), 121), '-', '), ': ', ', ', ', ', ', ', ', ' '.','')
Set @ScriptCount =0
/* Read script file content * *
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 (a) +char + n ' Go ' +char (a) +char + n '/* ' + @ScriptNr + ' (' +rtrim (@ScriptCount) + '): ' + @subdirectoryStr + ' * * * +CHAR (+char) + N ' Go ' +char +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 +char (a) + n ' Go ' +char +char (a) + n '/* ' + @ScriptNr + ' merge complete (total) +rtrim (@ScriptCount) + ' Each script file '. * * +char (+char) + N ' Go ' +char +char (10)
/* Write Merge script file * *
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 ' *********** error when reading and writing files *********** '
Exec @hr =sp_oageterrorinfo @object, @src out and @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 ' *********** merge script complete *********** '
Print N ' merged script file: ' + @NewFileName
End
Go
Before invoking the above stored procedure, you need to confirm that the OLE automation procedures and xp_cmdshell options are enabled:
Copy Code code as follows:
Before invoking the above stored procedure, you need to confirm that the OLE automation procedures and xp_cmdshell options are enabled:
Copy Code code as follows:
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
Test:
Copy Code code as follows:
Use master
Go
Exec Master.dbo.sp_ScriptMerge
@Path = ' C:\Users\Administrator\Desktop\temp ',--nvarchar (1024)
@FilesList = ',-nvarchar (max)
@NewFileName = ' Merge Script 20110905.sql '--nvarchar (1024)
Merge script completes *********** merged script file: C:\Users\Administrator\Desktop\temp\ merge Script 20110905.sql 2nd is a CLR stored procedure, using C # The code implements the merge SQL script file.
--------------------------------------------------------------------------------
C # code:
Copy Code code as follows:
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 = "Merge Script" + DateTime.Now.ToString ("yyyyMMdd") + ". sql";
}
SqlContext.Pipe.Send (Newfilename.tostring ()); Print a merged SQL file name
1. Get a list of SQL scripts
if (fileslist!= "")
{
Strfiles = Fileslist.split (', '); Sieve the list of SQL script file names, separated by ","
}
Else
{
Strfiles = new String[myfileinfo.length];
for (int i = 0; i < myfileinfo.length; i++)
{
Strfiles[i] = Myfileinfo[i]. Name;
}
}
2. Merge Scripts
SqlContext.Pipe.Send ("SQL script file list: \ n--------------------------------------------");
StreamWriter SW = new StreamWriter (Path + @ "\" + NewFileName, True, Encoding.unicode); Using Unicode encoding
SW. WriteLine (@ "go\n/*============" + Strscriptnr + "====start===================================*/\ngo\n"); Record the resulting merge script number & Merge action start position
foreach (String strfile in Strfiles)
{
if (strfile!=newfilename)
{
intcount + 1;
SW. WriteLine (@ "/*" + STRSCRIPTNR +@ "(" +intcount+@ "):" + strfile + "*/\ngo\n"); Record which script file is merged
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 ()); Print a merged SQL file name
}
}
SW. WriteLine (@ "/*============" + Strscriptnr + "====end (altogether" + intcount + "files) ===================================*/\ngo\n "); Record the number of merged script files generated & the end position of the merge action
SW. Close ();
SqlContext.Pipe.Send ("N/a" synthetic file ": \ n--------------------------------------------\ n" + newfilename);
}
catch (System.Exception e)
{
SqlContext.Pipe.Send ("\ n error occurred within method Sqlscriptmerge: \ n" + e.tostring ());
}
}
}
Stored Procedure Code:
Copy Code code as follows:
Use master
Go
--Start the CLR
Exec sp_configure ' clr enable ', 1
Go
Reconfigure
Go
--Set database options first
Alter Database Master Set trustworthy on
Go
--Stored procedures
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
The above CLR stored procedure Code is run under SQL Server & Microsoft Visual Studio 2005.
