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

[allanli]

不是用企业管理器导出SQL语句然后执行,这个我会.

看过zjcxc(邹建)写的解密存储过程的帖子,受益非浅,但有些地方不是太明白(可能太复杂了,呵呵)
我现在复制的是没有经过加密的,只是希望把某个数据库的所有存储过程,自定义函数,视图以及触发器复制到另一个数据库(其实我的目的是通过SQL语句在线更新某个数据库的所有存储过程,自定义函数,视图以及触发器)

期待斑竹zjcxc(邹建)的帮助,先谢了.

[pbsql]

建议你还是用企业管理器导出SQL语句然后执行，这样简单些

当然也可以用命令生成脚本，不过命令比较繁，估计你看了就不想用了：

如何用命令生成SQL SERVER脚本

--以下生成整个数据库的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 /?

/s — 指示要连接到的源服务器。
/d — 指示要为之编写脚本的源数据库。
/I — 使用集成安全性。
/P — sa 要用的密码。请注意登录 ID 始终为 sa。
若/P不使用或标志后面没有密码，
则将使用空密码。不与 /I 兼容。
/F — 脚本文件应生成到的目录。
这意味着为每个对象分类生成一个文件。
/f — 所有脚本将保存到的单个文件。
不与 /F 兼容。
/q — 在所生成的脚本中使用被引用的标识符。
/r — 为脚本中的对象包括 drop 语句。
/O — 生成 OEM 脚本文件。无法用于 /A 或 /T。
这是默认的行为。
/T — 生成 UNICODE 脚本文件。无法用于 /A 或 /O。
/A — 生成 ANSI 脚本文件。无法用于 /T 或 /O。
/? — 命令行帮助。
/E — 发生错误时停止脚本编写。
默认行为是记录该错误而后继续。
/C — 指示替代服务器 CodePage（代码页）的 CodePage。
/N — 生成 ANSI PADDING。
/X — 编写 SP 和 XP 脚本以分隔文件。
/H — 生成不带首部的脚本文件。（默认: 带首部）。
/G — 使用指定的服务器名称作为所生成的输出文件的前缀（
中的划线）。
/Y — 为“扩展属性”生成脚本（仅对 8.x 服务器有效）。

[zjcxc]

--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

[zjcxc]

--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

[allanli]

对两位斑竹真是感激到泪流满面,今生无以为报,只好......呵呵

我要的就是zjcxc(邹建)的方法,开始也是这样想直接拷贝syscomments和sysobjects表,但不敢肯定可不可以.
再问一下zjcxc(邹建),你建立sys_syscomments_bak表的目的是不是为了方便或者保险起见啊.可不可以直接不通过这个表呢?还有的是如果我的存储过程很长(有的多大2000多行),超过8000个字节从而分块储存了,重新编译的时候会不会出问题啊?

[zjcxc]

正是因为考虑到存储过程很长(有的多大2000多行),超过8000个字节,所以采用了操作系统表的方式来解决

直接操作也可以,不过这样的话,就要用到分布式事务处理,而分布式事务处理与操作系统的关联太多,不一定能调得出来,所以还是用一个中间表,放弃分布式事务处理比较好一点.

[zjcxc]

今生无以为报.... 千万别以身相许啊

[wonderyou]

高，学习！

[hb0746]

高,我不是很懂!

[liangx326]

高手高手高高手！！

[zhangzs8896]

收藏，学习！

[123456754321]

,

[luke5678]

MK

[funsuzhou]

学习