概述
——————————————————————————–
在平时的工作中，我会经常的碰到这样需要合并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 选项：
复制代码 代码如下:
调用上面存储过程前，需要确认启用 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下运行通过。