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

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