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

--以下生成整个数据库的SQL脚本，我测试了，相当好用。
--(scptxfr.exe的路径要正确以下是我的路径)
declare @cMd varchar(1000)
set @cmd = 'master.dbo.xp_cmdshell ' +
'''c:/"Microsoft ' +
'SQL Server"' +
'/MSSQL/Upgrade/scptxfr.exe ' +
' /s YourServerName /p YourSAPassword /I /d YourDBName /f ' +
'c:/YourDBName.sql'''
exec (@cmd)

SCPTXFR /s <服务器> /d <数据库> {[/I] | [/P <密码>]}
{[/F <脚本文件目录>] | [/f <单个脚本文件>]}
/q /r /O /T /A /E /C <CodePage> /N /X /H /G /Y /?