批量执行sql脚本

--批量执行sql脚本

--批量执行文件夹下面的所有sql脚本

 

create procedure exeSqlFileBatch 
( 
 @returnCode int=0 output, --返回错误数 
 @returnSucceed int=0 output, --成功执行文件数量 
 @serverName varchar(1000), --服务器名称 
 @dataBase varchar(500), --数据库名称 
 @uid varchar(50), --用户名 
 @pwd  varchar(50), --密码 
 @filePath varchar(5000)  --文件路径 
) 

with encryption as  
declare @strsql varchar(6000) 
set @strsql=' dir '+@filePath+'\*.sql' 

--创建临时表 
create table #tmptb(fileDes varchar(5000))
 
--将文件夹信息插入临时表 
insert into #tmptb 
exec master.dbo.xp_cmdshell @strsql 
if @@error  <> 0  
begin  
 set @returnCode=1 
 return @returnCode 
end  

--从临时表中提取有用的记录 
select dbo.f_split(fileDes,' ')as fileDes 
into #tmepUse from #tmptb 
where fileDes like '%.sql' 

--定义游标 
declare  fetFileName cursor 
for select fileDes from #tmepUse 
declare @strFileName varchar(2000) 
declare @reExe int 
--定义错误信息表 

create table #errorTable(errorFileName varchar(1000)) 

--打开游标并历遍进行取值 
open fetFileName 
fetch next from fetFileName into @strFileName 
while @@FETCH_STATUS = 0 
begin   
 --执行该数据脚本文件 
 set @strsql='osql -S '+@serverName 
      +' -U '+@uid +' -P '+@pwd +' -d '+@dataBase +' -i ' 
      +@filePath +'\'+@strFileName 
 exec @reExe=master.dbo.xp_cmdShell @strsql 

 --统计执行错误数执行成功数 
 if @reExe=0  
  set @returnSucceed=@returnSucceed+1 
 else 
  begin 
   --没办法，xp_cmdShell 总返回零。可能无法获取osql所发生的错误 
   insert #errorTable values(@strFileName) 
   set @returnCode=@returnCode+1 
  end  
 fetch next from fetFileName into @strFileName 
end  

close fetFileName 
deallocate fetFileName 
--返回错误错误信息  
select *from #errorTable 
return 
  

--测试 
declare @returnCode int  

declare @returnSucceed int  

set @returnCode=0 

set @returnSucceed=0 

exec exeSqlFileBatch @returnCode output,@returnSucceed output,'服务器名（或服务器IP）','数据库名','登录名','密码','sql文件路径（如：c:/）' 

print @returnCode 

print @returnSucceed 

--测试前请先创建函数 

/*------------------- 

--实现split功能 的函数 

--yangys 2005/07/04 

--只返回最后的一串 

*/ 

create function f_split( 
 @SourceSql varchar(8000),--字符串 

 @StrSeprate varchar(10)--分隔符 
) 
returns  varchar(1000) 
as  
begin 
    declare @i int 
    set @SourceSql=rtrim(ltrim(@SourceSql)) 
    set @i=charindex(@StrSeprate,@SourceSql) 
    while @i> =1 
    begin 
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) 
        set @i=charindex(@StrSeprate,@SourceSql) 
    end 
    return @SourceSql 
end 


-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO


--执行想要的xp_cmdshell语句
Exec xp_cmdshell 'query user'
GO

--用完后,要记得将xp_cmdshell禁用(出于安全考虑)
-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 禁用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
--重新配置
RECONFIGURE
GO