首页 / 数据库 / SQLServer / 将mater库中的系统存储过程批量生成*.sql文件 通用且非常实用

大家都知道系统存储过程是无法用工具导出的（大家可以试试 >任务>生成SQL脚本）
因为系统存储过程一般是不让开发人员修改的。

需要知识：

1、xp_cmdshell命令的使用
2、sp_MS_marksystemobject 标记系统存储过程的方法
3、dos 命令，如 type,>> 等
4、bcp 命令的使用
复制代码 代码如下:
use master
go
if OBJECT_ID（"pr_procToSql"） is not null drop proc pr_procToSql
go
create proc pr_procToSql
（
@服务器名 varchar（100）
,@用户名 varchar（100）
,@密码 varchar（100）
,@path varchar（200）
,@database varchar（200）
,@sysproc int="0" --是否标记为系统函数 1:是,0:否
,@proc_name varchar（100）="" --默认是所有,可以模糊搜索
,@savetype varchar（200）=".sql" --默认保存为sql脚本
）
as
/*
版本:v1
作者:达摩
日期:2012-04-13
功能:
1将master库的系统存储过程批量生成文件（系统存储过程无法自动导出）
2可以将所有类型的存储过程导出
3可以标记上系统存储过程
调用:
exec pr_procToSql ".","sa","H4ymH@$RTd","e: ommaster","master","1",‘"
exec pr_procToSql ".","sa","a123456","e:sql","agt_trad","","pr_",".sql"
*/
set nocount on
declare @sp nvarchar（500）,@s nvarchar（2000）,@row int,@id int,@s_add varchar（2000）
set @s=" use "+@database
exec（@s）
if object_id（"tempdb..#t"） is not null drop table tempdb..#t
create table tempdb..#t（name varchar（2000）
, id int IDENTITY（1,1） not null
）
exec（"
insert into tempdb..#t（name）
select name
--into TEMPDB..#T
from "+@database+"..sysobjects where xtype=""p"" and name like """+@proc_name+"%""
"）
select @row=COUNT（*） from tempdb..#t
print "共生成["+cast（@row as varchar）+"]个存储过程"
set @id=1
while @row>=@id
begin
select top 1 @sp=name from tempdb..#T where id=@id
if OBJECT_ID（"tempdb..test"） is not null drop table tempdb..test
--增加use master go
set @s_add="echo use ["+@database+"]>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo GO>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo IF EXISTS （SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N""[dbo].["+@sp+"]""） AND type in （N""P"", N""PC""））>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo DROP PROCEDURE [dbo].["+@sp+"]>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo GO>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo SET ANSI_NULLS ON>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo GO>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo SET QUOTED_IDENTIFIER ON>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo GO>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
select @s="
select text into tempdb..test
from "+@database+"..syscomments
where id=OBJECT_ID（"""+@database+".."+@sp+"""）
"
exec（@s）
--select * from tempdb..test
select @s="exec xp_cmdshell "+"""bcp tempdb..test out "+@path+@sp+cast（@id as varchar）+@savetype+" -c -S "+@服务器名+" -U "+@用户名+" -P "+@密码+""""
exec（@s）
--将前面加上use master 信息追加到 最前面
set @s_add="type "+@path+@sp+CAST（@id as varchar）+@savetype+">>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo GO>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
if @sysproc="1"
begin
--在最后面加上标记为系统存储过程
set @s_add="echo exec sp_MS_marksystemobject ""["+@sp+"]"">>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
set @s_add="echo GO>>"+@path+@sp+@savetype
exec xp_cmdshell @s_add
print "标记第["+cast（@id as varchar）+"]个为系统存储过程:"+@sp
end
set @s_add="del "+@path+@sp+CAST（@id as varchar）+@savetype
exec xp_cmdshell @s_add
print "生成第["+cast（@id as varchar）+"]个存储过程:"+@sp
delete from tempdb..#T where id=@id
set @id=@id+1
end

此存储过程可以完善的功能
1、生成视图
2、生成函数
3、生成指定库的表结构
4、生成指定库的约束，用于批量生成升级脚本
5、用于生成数据库中升级的脚本
欢迎大家帮我想想，还有别的办法吗？希望加QQ282329611交流。

生成结果如图：