将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:\tom\master\’,’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交流。

生成结果如图:

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐