不是用企业管理器导出SQL语句然后执行,这个我会.
复制的是没有经过加密的,只是希望把某个数据库的所有存储过程,自定义函数,视图以及触发器复制到另一个数据库(其实我的目的是通过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