--SQL 2000? select distinct object_name(id)from syscomments where text like '%t_spliteParameter%' 试试,先全部找出来,然后再想办法替换就OK了
SQL 2005 是不能直接修改系统表的.要通过DAC去才行的,可以参考小梁的文章
declare @from varchar(256), @to varchar(256) set @from = 't_spliteParameter' --欲修改的字符串 set @to = 'fn_spliteParameter' --修改后的字符串BEGIN DECLARE @sql VARCHAR(MAX),@sqlname sysname,@i int set @i=0 DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT REPLACE(definition,@from,@to),object_id FROM sys.sql_modules WHERE CHARINDEX(@from,definition)>0 OPEN cur FETCH NEXT FROM cur INTO @sql,@sqlname WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @sql='ALTER '+RIGHT(@sql,LEN(@sql)-CHARINDEX('CREATE ',@sql)-6), @sqlname=object_name(@sqlname) EXEC (@sql) if @@error=0 begin set @i=@i+1 print @sqlname + ' altered.' end else print @sqlname + ' not altered.' FETCH NEXT FROM cur INTO @sql,@sqlname END CLOSE cur DEALLOCATE cur
declare @from varchar(256), @to varchar(256) set @from = 't_spliteParameter' --欲修改的字符串 set @to = 'fn_spliteParameter' --修改后的字符串BEGIN DECLARE @sql VARCHAR(MAX),@sqlname sysname,@i int set @i=0 DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT REPLACE(definition,@from,@to),object_id FROM sys.sql_modules WHERE CHARINDEX(@from,definition)>0 OPEN cur FETCH NEXT FROM cur INTO @sql,@sqlname WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @sql=REPLACE(@sql,'create PROCEDURE','alter PROCEDURE'),--'ALTER '+RIGHT(@sql,LEN(@sql)-CHARINDEX('CREATE ',@sql)-6), @sqlname=object_name(@sqlname) PRINT @sql EXEC (@sql) if @@error=0 begin set @i=@i+1 print @sqlname + ' altered.' end else print @sqlname + ' not altered.' FETCH NEXT FROM cur INTO @sql,@sqlname END CLOSE cur DEALLOCATE cur
有个笨方法,就是将所有的存储过程全选alter到同一个SQL脚本中,将t_spliteParameter替换成你想要的fn_spliteParameter,再执行一下SQL就全部更新了(在执行之前需要先将t_spliteParameter存储过程换成fn_spliteParameter)。
select distinct object_name(id)from syscomments where text like '%t_spliteParameter%'
试试,先全部找出来,然后再想办法替换就OK了
declare @from varchar(256), @to varchar(256)
set @from = 't_spliteParameter' --欲修改的字符串
set @to = 'fn_spliteParameter' --修改后的字符串BEGIN
DECLARE @sql VARCHAR(MAX),@sqlname sysname,@i int
set @i=0 DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT REPLACE(definition,@from,@to),object_id
FROM sys.sql_modules
WHERE CHARINDEX(@from,definition)>0 OPEN cur
FETCH NEXT FROM cur INTO @sql,@sqlname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql='ALTER '+RIGHT(@sql,LEN(@sql)-CHARINDEX('CREATE ',@sql)-6),
@sqlname=object_name(@sqlname)
EXEC (@sql)
if @@error=0 begin set @i=@i+1 print @sqlname + ' altered.' end
else print @sqlname + ' not altered.'
FETCH NEXT FROM cur INTO @sql,@sqlname
END
CLOSE cur
DEALLOCATE cur
print str(@i) + ' altered'END
消息 102,级别 15,状态 1,第 1 行
'date' 附近有语法错误。
消息 111,级别 15,状态 1,第 18 行
'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
前加一个 print @sql 显示出来看一下就知道原因了,有特殊情况的话就再调整一下吧
set @from = 't_spliteParameter' --欲修改的字符串
set @to = 'fn_spliteParameter' --修改后的字符串BEGIN
DECLARE @sql VARCHAR(MAX),@sqlname sysname,@i int
set @i=0 DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT REPLACE(definition,@from,@to),object_id
FROM sys.sql_modules
WHERE CHARINDEX(@from,definition)>0 OPEN cur
FETCH NEXT FROM cur INTO @sql,@sqlname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql=REPLACE(@sql,'create PROCEDURE','alter PROCEDURE'),--'ALTER '+RIGHT(@sql,LEN(@sql)-CHARINDEX('CREATE ',@sql)-6),
@sqlname=object_name(@sqlname)
PRINT @sql
EXEC (@sql)
if @@error=0 begin set @i=@i+1 print @sqlname + ' altered.' end
else print @sqlname + ' not altered.'
FETCH NEXT FROM cur INTO @sql,@sqlname
END
CLOSE cur
DEALLOCATE cur
print str(@i) + ' altered'END
谢谢你朋友, 是这样的,这里应该 用 replace 将 create 替换为 alter,
如果 直接 用限定长度的字符串截取,长度 就不能准确判断了。