用sql语句复制存储过程

[飞牛]

现在想将数据库结构移到另一个数据库
使用下面的方法复制一般的存储过程没什么问题，如果存储过程太大（超过最大字节数8060）按下面的方法就没法复制了
不知道有没有什么解决办法
希望各位大侠帮忙看看

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
  
 /*生成表数据脚本的通用存储过程                  邹建 (引用请保留此信息)--*/

 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

[tianhuo_soft]

用DTS

[飞牛]

数据库都是sqlserver2000
导出来的语句没法直接用，需要调整后才可以执行
所以想写个存储过程然后用应用程序直接调用

[sp4]

写个存储过程肯定是不行的了

[sp4]

超过8000长。变量就会截断，出现错误。

[等不到来世]

try:

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
  
 /*生成表数据脚本的通用存储过程                  邹建 (引用请保留此信息)--*/

 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 text)
--
--select o.name,o.xtype,c.number,c.colid,c.status,c.ctext into #sys_syscomments_bak
--from dbo.syscomments c,dbo.sysobjects o where 1=2

 exec('
 insert #sys_syscomments_bak
     (name,xtype,number,colid,status,ctext)
 select o.name,o.xtype,c.number,c.colid,c.status,cast(c.ctext as varchar(8000))
 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],cast(cast(ob.[ctext] as varchar(8000)) as varbinary(8000))
     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

用text及varchar类型过渡一下。再试试？

[无心雨云]

帮顶
学习中

[zzuyongp]

脚本 运行 结束

[飞牛]

用text及varchar类型过渡一下 。再试试？
----------------------------------
这样应该就OK了，大概的看了几个存储过程都可以完整的复制过来
终于可以进行下一步了

[linguojin11]

帮顶。 。难哦

[zero8500]

DTS

[飞牛]

问题还没解决，哪个在帮忙看看那

[飞牛]

顶起来，别沉了

[hsie168518]

这个是好东西啊

垮服务器的可以吗？
--exec sp_copyProce [192.168.1.1].dbo.db1,[192.168.1.2].dbo.db1

[水族杰纶]

[Quote=引用 6 楼 ggqq74 的回复:]
数据库都是sqlserver2000
导出来的语句没法直接用，需要调整后才可以执行
所以想写个存储过程然后用应用程序直接调用
[/Quote]
腳本沒法直接執行?

[飞牛]

垮服务器也不行 ，也是说超出来最大行

[飞牛]

[Quote=引用 10 楼 wufeng4552 的回复:]
引用 6 楼 ggqq74 的回复:
数据库都是sqlserver2000
导出来的语句没法直接用，需要调整后才可以执行
所以想写个存储过程然后用应用程序直接调用
-------------------------------
腳本沒法直接執行?
[/Quote]
因为在某些存储过程中有可能还要用到其他的函数或存储过程，所以就需要将一些函数或存储过程创建顺序做调整

[hsie168518]

[Quote=引用 11 楼 ggqq74 的回复:]
垮服务器也不行 ，也是说超出来最大行
[/Quote]


那我就不要了，还是导出脚本NB

[等不到来世]

try:

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
  
 /*生成表数据脚本的通用存储过程                  邹建 (引用请保留此信息)--*/

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

select o.name,o.xtype,c.number,c.colid,c.status,c.ctext into #sys_syscomments_bak
from dbo.syscomments c,dbo.sysobjects o where 1=2

 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

[等不到来世]

改变了一下临时表的生成方式，使得ctext与系统表类型完全一致，试试看……

[飞牛]

[Quote=引用 15 楼 szx1999 的回复:]
改变了一下临时表的生成方式，使得ctext与系统表类型完全一致，试试看……
[/Quote]
服务器: 消息 511，级别 16，状态 1，行 2
无法创建大小为 8079 的行，该值大于允许的最大值 8060。