--* 感謝CSDN的zjcxc(: 邹建 :)
declare @o_para varchar(100),@n_para varchar(100)
select @o_para='@resname varchar(10)' --你的存储过程中,参数原来的定义
,@n_para='@resname varchar(100)' --新的参数定义
declare tb cursor for
select name=object_name(id),id,colid
from syscomments
where objectproperty(id,'IsProcedure')=1 and status=0
and text like '%'+@o_para+'%'
order by name
declare @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,'''
+@o_para+''','''+@n_para+''')
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,'''
+@o_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
