将一个数据库中的存储过程,复制到另一数据库

use master
2

go
3

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_copyProce]') and
5

OBJECTPROPERTY(id, N'IsProcedure') = 1)
7

drop procedure [dbo].[sp_copyProce]
8

GO
9

/*--生成表数据脚本的通用存储过程,
11

功能:将一个数据库中的存储过程,复制到另一数据库中
13

目标数据库中已经有的存储过程不覆盖
14

--邹建 2005.01(引用请保留此信息)--*/
16

/*--调用示例
18

exec master.dbo.sp_copyProce 'a','b'
20

--*/
21

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

--复制存储过程信息到临时表
41

create table #sys_syscomments_bak(name sysname,xtype char(2),number smallint,colid
42

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

--创建存储过程
57

declare tb cursor local for
58

select 'use '+@d_dbname+' exec(''create proc dbo.['+replace(name,N']',N']]')+'] as --'')
59

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

--复制存储过程结构
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

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

--使用测试
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