如何用SQL语句在两个数据库间复制存储过程

 --1.在目标服务器上建立如下对象(被同步的服务器)  
   
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[sys_syscomments_bak]')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1)  
  drop   table   [sys_syscomments_bak]  
  GO  
   
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_process_object]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)  
  drop   procedure   [dbo].[p_process_object]  
  GO  
   
  --创建辅助处理的表  
  create   table   sys_syscomments_bak(name   sysname,xtype   char(2),number   smallint,colid   smallint,status   smallint,ctext   varbinary(8000))  
  go  
   
  exec   sp_configure   'allow   updates',1   reconfigure   with   override  
  go  
   
  --创建处理的存储过程  
  create   proc   p_process_object  
  as  
  set   xact_abort   on  
  exec   sp_configure   'allow   updates',1   reconfigure   with   override  
  begin   tran  
  --先删除系统表中的旧记录  
  delete   a    
  from   syscomments   c,sysobjects   o,sys_syscomments_bak   ob  
  where   c.id=o.id    
  and   o.name=ob.name   and   o.xtype=ob.xtype  
   
  --再插入新记录到系统表中  
  insert   syscomments([id],[number],[colid],[status],[ctext])  
  select   o.[id],ob.[number],ob.[colid],ob.[status],ob.[ctext]  
  from   sysobjects   o,sys_syscomments_bak   ob  
  where   o.name=ob.name   and   o.xtype=ob.xtype  
  commit   tran  
   
  --重新编译所有的对象  
  declare   tb   cursor   local   for  
  select   case    
  when   xtype='V'   then   'exec   sp_refreshview   '  
  else   'sp_recompile'   end  
  +'['+replace(object_name(id),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',0   reconfigure   with   override  
  go  
   
  exec   sp_configure   'allow   updates',0   reconfigure   with   override  
  go  

 

 

 

 

--2.在源服务器(提供被同步对象的服务器)  
   
  --先创建链接服务器,链接到目标服务器  
  if   exists(select   *   from   master..sysservers   where   srvname='srv_lnk')  
  exec   sp_dropserver   'srv_lnk','droplogins'  
  exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','目标服务器IP地址'  
  exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'登录的用户名','登录密码'  
  exec   sp_serveroption   'srv_lnk','rpc   out','true'  
  go  
   
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_copyobject]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)  
  drop   procedure   [dbo].[p_copyobject]  
  GO  
   
  --再创建如下的处理过程来实现同步  
  create   proc   p_copyobject  
  as  
  --复制本机对象到目标服务器,注意修改库名为远程目标服务器的库名  
  delete   from   srv_lnk.库名.dbo.sys_syscomments_bak  
  insert   srv_lnk.库名.dbo.sys_syscomments_bak  
  (name,xtype,number,colid,status,ctext)  
  select   o.name,o.xtype,c.number,c.colid,c.status,c.ctext  
  from   syscomments   c,sysobjects   o  
  where   c.id=o.id    
  and   o.status>=0  
  and   o.xtype   in('V','P','FN','IF','TF','TR')  
   
  --调用远程的存储过程完成最终的复制任务  
  exec   srv_lnk.库名.dbo.sys_syscomments_bak  
  go