1![]()
use master
2![]()
go
3![]()
4![]()
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_copyProce]') and
5![]()
6![]()
OBJECTPROPERTY(id, N'IsProcedure') = 1)
7![]()
drop procedure [dbo].[sp_copyProce]
8![]()
GO
9![]()
10![]()
/*--生成表数据脚本的通用存储过程,
11![]()
12![]()
功能:将一个数据库中的存储过程,复制到另一数据库中
13![]()
目标数据库中已经有的存储过程不覆盖
14![]()
15![]()
--邹建 2005.01(引用请保留此信息)--*/
16![]()
17![]()
/*--调用示例
18![]()
19![]()
exec master.dbo.sp_copyProce 'a','b'
20![]()
--*/
21![]()
22![]()
create proc sp_copyProce
23![]()
@s_dbname sysname, --要复制存储过程的源数据库名
24![]()
@d_dbname sysname --目标数据库名
25![]()
as
26![]()
set nocount on
27![]()
if db_id(@s_dbname) is null
28![]()
begin
29![]()
raiserror('数据库"%s"不存在',1,16,@s_dbname)
30![]()
return
31![]()
end
32![]()
if db_id(@d_dbname) is null
33![]()
begin
34![]()
raiserror('数据库"%s"不存在',1,16,@d_dbname)
35![]()
return
36![]()
end
37![]()
select @s_dbname='['+replace(@s_dbname,']',']]')+']'
38![]()
,@d_dbname='['+replace(@d_dbname,']',']]')+']'
39![]()
40![]()
--复制存储过程信息到临时表
41![]()
create table #sys_syscomments_bak(name sysname,xtype char(2),number smallint,colid
42![]()
43![]()
smallint,status smallint,ctext varbinary(8000))
44![]()
exec('
45![]()
insert #sys_syscomments_bak
46![]()
(name,xtype,number,colid,status,ctext)
47![]()
select o.name,o.xtype,c.number,c.colid,c.status,c.ctext
48![]()
from '+@s_dbname+'.dbo.syscomments c,'+@s_dbname+'.dbo.sysobjects o
49![]()
where c.id=o.id
50![]()
and o.status>=0
51![]()
and o.xtype=''P''
52![]()
and not exists(
53![]()
select * from '+@d_dbname+'.dbo.sysobjects where name=o.name)
54![]()
')
55![]()
56![]()
--创建存储过程
57![]()
declare tb cursor local for
58![]()
select 'use '+@d_dbname+' exec(''create proc dbo.['+replace(name,N']',N']]')+'] as --'')
59![]()
60![]()
exec sp_recompile ['+replace(name,N']',N']]')+']'
61![]()
from #sys_syscomments_bak
62![]()
declare @s nvarchar(4000)
63![]()
open tb
64![]()
fetch tb into @s
65![]()
while @@fetch_status=0
66![]()
begin
67![]()
exec(@s)
68![]()
fetch tb into @s
69![]()
end
70![]()
close tb
71![]()
deallocate tb
72![]()
73![]()
--复制存储过程结构
74![]()
exec sp_configure 'allow updates',1 reconfigure with override
75![]()
begin tran
76![]()
exec('
77![]()
delete c
78![]()
from '+@d_dbname+'.dbo.syscomments c,'+@d_dbname+'.dbo.sysobjects
79![]()
80![]()
o,#sys_syscomments_bak ob
81![]()
where c.id=o.id and o.name=ob.name and o.xtype=ob.xtype
82![]()
insert '+@d_dbname+'.dbo.syscomments([id],[number],[colid],[status],[ctext])
83![]()
select o.[id],ob.[number],ob.[colid],ob.[status],ob.[ctext]
84![]()
from '+@d_dbname+'.dbo.sysobjects o,#sys_syscomments_bak ob
85![]()
where o.name=ob.name and o.xtype=ob.xtype')
86![]()
commit tran
87![]()
exec sp_configure 'allow updates',0 reconfigure with override
88![]()
go
89![]()
90![]()
91![]()
--使用测试
92![]()
create database a
93![]()
go
94![]()
use a
95![]()
go
96![]()
create proc p_test1
97![]()
as
98![]()
select 'test1'
99![]()
go
100![]()
101![]()
102![]()
create proc p_test2
103![]()
as
104![]()
select 'test2'
105![]()
go
106![]()
107![]()
create database b
108![]()
go
109![]()
110![]()
exec master.dbo.sp_copyProce 'a','b'
111![]()
go
112![]()
select * from b.dbo.sysobjects where xtype='P'
113![]()
114![]()
exec b.dbo.p_test1
115![]()
exec b.dbo.p_test2
116![]()
go
117![]()
118![]()
use master
119![]()
go
120![]()
121![]()
drop database a,b
122![]()
drop proc sp_copyProce
use master2
go3
4
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_copyProce]') and 5
6
OBJECTPROPERTY(id, N'IsProcedure') = 1)7
drop procedure [dbo].[sp_copyProce]8
GO9
10
/*--生成表数据脚本的通用存储过程,
11
12
功能:将一个数据库中的存储过程,复制到另一数据库中13
目标数据库中已经有的存储过程不覆盖14
15
--邹建 2005.01(引用请保留此信息)--*/16
17
/*--调用示例18
19
exec master.dbo.sp_copyProce 'a','b'20
--*/21
22
create proc sp_copyProce23
@s_dbname sysname, --要复制存储过程的源数据库名24
@d_dbname sysname --目标数据库名25
as26
set nocount on27
if db_id(@s_dbname) is null28
begin29
raiserror('数据库"%s"不存在',1,16,@s_dbname)30
return31
end32
if db_id(@d_dbname) is null33
begin34
raiserror('数据库"%s"不存在',1,16,@d_dbname)35
return36
end37
select @s_dbname='['+replace(@s_dbname,']',']]')+']'38
,@d_dbname='['+replace(@d_dbname,']',']]')+']'39
40
--复制存储过程信息到临时表41
create table #sys_syscomments_bak(name sysname,xtype char(2),number smallint,colid 42
43
smallint,status smallint,ctext varbinary(8000))44
exec('45
insert #sys_syscomments_bak46
(name,xtype,number,colid,status,ctext)47
select o.name,o.xtype,c.number,c.colid,c.status,c.ctext48
from '+@s_dbname+'.dbo.syscomments c,'+@s_dbname+'.dbo.sysobjects o49
where c.id=o.id 50
and o.status>=051
and o.xtype=''P''52
and not exists(53
select * from '+@d_dbname+'.dbo.sysobjects where name=o.name)54
')55
56
--创建存储过程57
declare tb cursor local for58
select 'use '+@d_dbname+' exec(''create proc dbo.['+replace(name,N']',N']]')+'] as --'') 59
60
exec sp_recompile ['+replace(name,N']',N']]')+']'61
from #sys_syscomments_bak62
declare @s nvarchar(4000)63
open tb64
fetch tb into @s65
while @@fetch_status=066
begin67
exec(@s)68
fetch tb into @s69
end70
close tb71
deallocate tb72
73
--复制存储过程结构74
exec sp_configure 'allow updates',1 reconfigure with override75
begin tran76
exec('77
delete c78
from '+@d_dbname+'.dbo.syscomments c,'+@d_dbname+'.dbo.sysobjects 79
80
o,#sys_syscomments_bak ob81
where c.id=o.id and o.name=ob.name and o.xtype=ob.xtype82
insert '+@d_dbname+'.dbo.syscomments([id],[number],[colid],[status],[ctext])83
select o.[id],ob.[number],ob.[colid],ob.[status],ob.[ctext]84
from '+@d_dbname+'.dbo.sysobjects o,#sys_syscomments_bak ob85
where o.name=ob.name and o.xtype=ob.xtype')86
commit tran87
exec sp_configure 'allow updates',0 reconfigure with override88
go89
90
91
--使用测试92
create database a93
go94
use a95
go96
create proc p_test197
as98
select 'test1'99
go100
101
102
create proc p_test2103
as104
select 'test2'105
go106
107
create database b108
go109
110
exec master.dbo.sp_copyProce 'a','b'111
go112
select * from b.dbo.sysobjects where xtype='P'113
114
exec b.dbo.p_test1115
exec b.dbo.p_test2116
go117
118
use master119
go120
121
drop database a,b122
drop proc sp_copyProce
浙公网安备 33010602011771号