How to merge SQL script files

Overview
--------------------------------------------------------------------------------
In my daily work, I often encounter the problem of merging SQL scripts. For example, there are many SQL script files that need to be generated in a certain order into a combined SQL script file, and then published to the user's SQL Server.
The most direct way to merge SQL script files is to create a blank SQL script file, and then copy the content of the SQL script file to be merged to the new SQL file. At the beginning, the operations for merging scripts were similar to those just mentioned. I create a query in Microsoft SQL Server Management Studio (MSSMS), and then open the SQL script files to be merged one by one, and copy the content to the new query, then generate the merge script file.
The above method seems to be okay for merging several SQL script files. However, there are a lot of scripts to be merged, and it is complicated to copy the content of a file or a file. It would be better to have a simple script merge tool. The following describes the stored procedures for merging SQL script files.
For 1st stored procedures, you only need to call sp_OACreate to merge SQL script files. Copy codeThe Code is 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 the 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 (512), depth smallint, 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 (convert (nvarchar (23), getdate (), 121 ),'-',''), ':',''),'',''),'. ','')
Set @ ScriptCount = 0
/* Read the content of the script file */
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
Select a. subdirectory
From @ subdirectoryTB As
Left Join @ tmp As B ON B. fileName = a. subdirectory
Where a. = 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 +' merged (total '+ rtrim (@ ScriptCount) +' script files ). */'+ Char (13) + Char (10) + n' Go' + Char (13) + Char (10)
/* Write the merged 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'************* An error occurred while reading and writing files ***********'
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'************* merge the script ***********'
Print n' merged script file: '+ @ NewFileName
End
Go

Before calling the preceding stored procedure, make sure to enable the OLE Automation Procedures and xp_cmdshell options:Copy codeThe Code is as follows:

Before calling the preceding stored procedure, make sure to enable the OLE Automation Procedures and xp_cmdshell options:Copy codeThe Code is 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 codeThe Code is 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)

* ********************** The merged script file: C: \ Users \ Administrator \ Desktop \ temp \ merge script 20110905. SQL 2nd is a CLR Stored Procedure. Use C # code to merge SQL script files.
--------------------------------------------------------------------------------
C # code:Copy codeThe Code is 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 the merged SQL file name
// 1. Obtain the SQL script list
If (FilesList! = "")
{
StrFiles = FilesList. Split (','); // screening the SQL script file name list, separated ","
}
Else
{
StrFiles = new string [myFileInfo. Length];
For (int I = 0; I <myFileInfo. Length; I ++)
{
StrFiles [I] = myFileInfo [I]. Name;
}
}
// 2. Merge the script
SqlContext. Pipe. Send ("[SQL script file list]: \ n --------------------------------------------");
StreamWriter SW = new StreamWriter (Path + @ "\" + NewFileName, true, Encoding. Unicode); // use Unicode Encoding
SW. writeLine (@ "Go \ n/* =" + strScriptNr + "= Start = =========================================*/\ nGo \ n "); // record the generated merged script number and the start position of the merged action
Foreach (string strFile in strFiles)
{
If (strFile! = NewFileName)
{
IntCount + = 1;
SW. writeLine (@ "/*" + strScriptNr + @ "(" + intCount + @ "):" + strFile + "*/\ nGo \ n "); // record which script file to merge
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 the merged SQL file name
}
}
SW. writeLine (@ "/* =" + strScriptNr + "= End (total" + intCount + "files) ========================================== */\ nGo \ n "); // record the number of merged script files and the end position of the merged action
SW. Close ();
SqlContext. Pipe. Send ("\ n [post-merging file]: \ n ---------------------------------------------- \ n" + NewFileName );
}
Catch (System. Exception e)
{
SqlContext. Pipe. Send ("\ n error in method SQLScriptMerge: \ n" + e. ToString ());
}
}
}

Stored Procedure Code:Copy codeThe Code is as follows: Use master
GO
-- Start CLR
Exec sp_configure 'clr enable', 1
Go
Reconfigure
GO
-- Set database options first
Alter Database Master Set TRUSTWORTHY On
Go
-- Stored Procedure
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 \ 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 preceding CLR Stored Procedure Code is run under SQL Server 2005 & Microsoft Visual Studio 2005.