use master
go
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[sp_copyProce] ') and
OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[sp_copyProce]
GO
/*--生成表数据脚本的通用存储过程,
功能:将一个数据库中的存储过程,复制到另一数据库中
目标数据库中已经有的存储过程不覆盖
2005.01(引用请保留此信息)--*/
/*--调用示例
exec master.dbo.sp_copyProce 'a ', 'b '
--*/
create proc sp_copyProce
@s_dbname sysname, --要复制存储过程的源数据库名
@d_dbname sysname --目标数据库名
as
set nocount on
if db_id(@s_dbname) is null
begin
raiserror( '数据库 "%s "不存在 ',1,16,@s_dbname)
return
end
if db_id(@d_dbname) is null
begin
raiserror( '数据库 "%s "不存在 ',1,16,@d_dbname)
return
end
select @s_dbname= '[ '+replace(@s_dbname, '] ', ']] ')+ '] '
,@d_dbname= '[ '+replace(@d_dbname, '] ', ']] ')+ '] '
--复制存储过程信息到临时表
create table #sys_syscomments_bak(name sysname,xtype char(2),number smallint,colid
smallint,status smallint,ctext varbinary(8000))
exec( '
insert #sys_syscomments_bak
(name,xtype,number,colid,status,ctext)
select o.name,o.xtype,c.number,c.colid,c.status,c.ctext
from '+@s_dbname+ '.dbo.syscomments c, '+@s_dbname+ '.dbo.sysobjects o
where c.id=o.id
and o.status> =0
and o.xtype= ' 'P ' '
and not exists(
select * from '+@d_dbname+ '.dbo.sysobjects where name=o.name)
')
--创建存储过程
declare tb cursor local for
select 'use '+@d_dbname+ ' exec( ' 'create proc dbo.[ '+replace(name,N '] ',N ']] ')+ '] as -- ' ')
exec sp_recompile [ '+replace(name,N '] ',N ']] ')+ '] '
from #sys_syscomments_bak
declare @s nvarchar(4000)
open tb
fetch tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch tb into @s
end
close tb
deallocate tb
--复制存储过程结构
exec sp_configure 'allow updates ',1 reconfigure with override
begin tran
exec( '
delete c
from '+@d_dbname+ '.dbo.syscomments c, '+@d_dbname+ '.dbo.sysobjects
o,#sys_syscomments_bak ob
where c.id=o.id and o.name=ob.name and o.xtype=ob.xtype
insert '+@d_dbname+ '.dbo.syscomments([id],[number],[colid],[status],[ctext])
select o.[id],ob.[number],ob.[colid],ob.[status],ob.[ctext]
from '+@d_dbname+ '.dbo.sysobjects o,#sys_syscomments_bak ob
where o.name=ob.name and o.xtype=ob.xtype ')
commit tran
exec sp_configure 'allow updates ',0 reconfigure with override
go
--使用测试
create database a
go
use a
go
create proc p_test1
as
select 'test1 '
go
create proc p_test2
as
select 'test2 '
go
create database b
go
exec master.dbo.sp_copyProce 'a ', 'b '
go
select * from b.dbo.sysobjects where xtype= 'P '
exec b.dbo.p_test1
exec b.dbo.p_test2
go
use master
go
drop database a,b
drop proc sp_copyProce
go
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[sp_copyProce] ') and
OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[sp_copyProce]
GO
/*--生成表数据脚本的通用存储过程,
功能:将一个数据库中的存储过程,复制到另一数据库中
目标数据库中已经有的存储过程不覆盖
2005.01(引用请保留此信息)--*/
/*--调用示例
exec master.dbo.sp_copyProce 'a ', 'b '
--*/
create proc sp_copyProce
@s_dbname sysname, --要复制存储过程的源数据库名
@d_dbname sysname --目标数据库名
as
set nocount on
if db_id(@s_dbname) is null
begin
raiserror( '数据库 "%s "不存在 ',1,16,@s_dbname)
return
end
if db_id(@d_dbname) is null
begin
raiserror( '数据库 "%s "不存在 ',1,16,@d_dbname)
return
end
select @s_dbname= '[ '+replace(@s_dbname, '] ', ']] ')+ '] '
,@d_dbname= '[ '+replace(@d_dbname, '] ', ']] ')+ '] '
--复制存储过程信息到临时表
create table #sys_syscomments_bak(name sysname,xtype char(2),number smallint,colid
smallint,status smallint,ctext varbinary(8000))
exec( '
insert #sys_syscomments_bak
(name,xtype,number,colid,status,ctext)
select o.name,o.xtype,c.number,c.colid,c.status,c.ctext
from '+@s_dbname+ '.dbo.syscomments c, '+@s_dbname+ '.dbo.sysobjects o
where c.id=o.id
and o.status> =0
and o.xtype= ' 'P ' '
and not exists(
select * from '+@d_dbname+ '.dbo.sysobjects where name=o.name)
')
--创建存储过程
declare tb cursor local for
select 'use '+@d_dbname+ ' exec( ' 'create proc dbo.[ '+replace(name,N '] ',N ']] ')+ '] as -- ' ')
exec sp_recompile [ '+replace(name,N '] ',N ']] ')+ '] '
from #sys_syscomments_bak
declare @s nvarchar(4000)
open tb
fetch tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch tb into @s
end
close tb
deallocate tb
--复制存储过程结构
exec sp_configure 'allow updates ',1 reconfigure with override
begin tran
exec( '
delete c
from '+@d_dbname+ '.dbo.syscomments c, '+@d_dbname+ '.dbo.sysobjects
o,#sys_syscomments_bak ob
where c.id=o.id and o.name=ob.name and o.xtype=ob.xtype
insert '+@d_dbname+ '.dbo.syscomments([id],[number],[colid],[status],[ctext])
select o.[id],ob.[number],ob.[colid],ob.[status],ob.[ctext]
from '+@d_dbname+ '.dbo.sysobjects o,#sys_syscomments_bak ob
where o.name=ob.name and o.xtype=ob.xtype ')
commit tran
exec sp_configure 'allow updates ',0 reconfigure with override
go
--使用测试
create database a
go
use a
go
create proc p_test1
as
select 'test1 '
go
create proc p_test2
as
select 'test2 '
go
create database b
go
exec master.dbo.sp_copyProce 'a ', 'b '
go
select * from b.dbo.sysobjects where xtype= 'P '
exec b.dbo.p_test1
exec b.dbo.p_test2
go
use master
go
drop database a,b
drop proc sp_copyProce