歸並SQL劇本文件的辦法分享。本站提示廣大學習愛好者：（歸並SQL劇本文件的辦法分享）文章只能為提供參考，不一定能成為您想要的結果。以下是歸並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: '+WordStr(replace(WordStr(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 選項：
挪用下面存儲進程前，須要確認啟用 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下運轉經由過程。
