1: 如上 sp_MSForEachTable 'alter table ? alter column resname varchar(200)'2:太难了。
我试了一下,好像不管用,我用的是SQL SERVER 7.0的。
怎么没人关注呀! 1. sp_MSForEachTable 'alter table ? alter column resname varchar(200)' 提示出错: Server: Msg 4924, Level 16, State 1, Line 1 ALTER TABLE ALTER COLUMN failed because column 'orderform' does not exist in table 'chmachine' 并且确实也没有改变resname的长度. 2. 我有个想法,不知好不好,就是装所有的存储过程存进一张表中,然后在表中进行查询,修改,再执行,不过有没有很好的办法把所有的存储过程中的数据一次性存入表中!大侠帮助,UP 表结果为:restore(resname varchar(100),restores ..[这里不知用什么类型比较好])
--用下面的方法 --修改表 declare tb cursor for SELECT 'alter table ['+object_name(a.id) +' alter column resname ' +b.name+'(200)' from syscolumns a left join systypes b on a.xtype=b.xusertype where a.name='resname'declare @s varchar(8000) open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tb
--调整存储过程(速度比较慢)declare @para varchar(100) set @para='@resname varchar(10)' --你的存储过程中,参数原来的定义declare tb cursor for select name=object_name(id),id,colid from syscomments where objectproperty(id,'IsProcedure')=1 and status=0 and text like '%'+@para+'%' order by namedeclare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000) ,@name sysname,@id int,@colid int,@oid int open tb fetch next from tb into @name,@id,@colid select @s2='',@s3='' while @@fetch_status=0 begin if @oid<>@id begin exec(@s1) exec(@s2+' '+@s3+' '+@s4+')') select @s1='drop proc ['+@name+']' ,@s2='declare @'+cast(@colid as varchar)+' varchar(8000)' ,@s3='select @'+cast(@colid as varchar)+'=replace(text,''' +@para+''',''@resname varchar(200)'') from syscomments where id='+cast(@id as varchar) +' and colid='+cast(@colid as varchar) ,@s4='exec(@'+cast(@colid as varchar) end else select @s2=@s2+',@'+cast(@colid as varchar)+' varchar(8000)' ,@s3=@s3+' select @'+cast(@colid as varchar)+'=replace(text,''' +@para+''',''@resname varchar(200)'') from syscomments where id='+cast(@id as varchar) +' and colid='+cast(@colid as varchar) ,@s4=@s4+'+@'+cast(@colid as varchar) set @oid=@id fetch next from tb into @name,@id,@colid end close tb deallocate tb
1.
sp_MSForEachTable 'alter table ? alter column resname varchar(200)'
提示出错:
Server: Msg 4924, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN failed because column 'orderform' does not exist in table 'chmachine'
并且确实也没有改变resname的长度.
2.
我有个想法,不知好不好,就是装所有的存储过程存进一张表中,然后在表中进行查询,修改,再执行,不过有没有很好的办法把所有的存储过程中的数据一次性存入表中!大侠帮助,UP
表结果为:restore(resname varchar(100),restores ..[这里不知用什么类型比较好])
--修改表
declare tb cursor for
SELECT 'alter table ['+object_name(a.id)
+' alter column resname '
+b.name+'(200)'
from syscolumns a left join systypes b on a.xtype=b.xusertype
where a.name='resname'declare @s varchar(8000)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
:)
把存储过程导出成SQL文本
然后用Editplus或者记事本也行
点编辑,查找,替换
修改以后执行一次就行了
set @para='@resname varchar(10)' --你的存储过程中,参数原来的定义declare tb cursor for
select name=object_name(id),id,colid
from syscomments
where objectproperty(id,'IsProcedure')=1 and status=0
and text like '%'+@para+'%'
order by namedeclare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000)
,@name sysname,@id int,@colid int,@oid int
open tb
fetch next from tb into @name,@id,@colid
select @s2='',@s3=''
while @@fetch_status=0
begin
if @oid<>@id
begin
exec(@s1)
exec(@s2+'
'+@s3+'
'+@s4+')')
select @s1='drop proc ['+@name+']'
,@s2='declare @'+cast(@colid as varchar)+' varchar(8000)'
,@s3='select @'+cast(@colid as varchar)+'=replace(text,'''
+@para+''',''@resname varchar(200)'')
from syscomments where id='+cast(@id as varchar)
+' and colid='+cast(@colid as varchar)
,@s4='exec(@'+cast(@colid as varchar)
end
else
select @s2=@s2+',@'+cast(@colid as varchar)+' varchar(8000)'
,@s3=@s3+'
select @'+cast(@colid as varchar)+'=replace(text,'''
+@para+''',''@resname varchar(200)'')
from syscomments where id='+cast(@id as varchar)
+' and colid='+cast(@colid as varchar)
,@s4=@s4+'+@'+cast(@colid as varchar)
set @oid=@id
fetch next from tb into @name,@id,@colid
end
close tb
deallocate tb