MsSql教程网-sqlserver 批量删除存储过程和批量修改存储过程的语句

修改：


复制代码 代码如下:

declare proccur cursor

for

select [name] from sysobjects where name like ‘Foods_%’

declare @procname varchar(100)

declare @temp varchar(100)

open proccur

fetch next from proccur into @procname

while(@@FETCH_STATUS = 0)

begin

set @temp=’kcb_’+@procname

EXEC SP_RENAME @procname,@temp

print(@procname + ‘已被删除’)

fetch next from proccur into @procname

end

close proccur

deallocate proccur

declare proccur cursor

for

select [name] from sysobjects where name like ‘kcb%’

declare @procname varchar(100)

declare @temp varchar(100)

declare @temp2 varchar(100)

declare @temp3 varchar(100)

open proccur

fetch next from proccur into @procname

while(@@FETCH_STATUS = 0)

begin

set @temp3= LEN(@procname)

set @temp=’kcb_’

set @temp2=RIGHT(@procname,@temp3-3)

set @temp+=@temp2

EXEC SP_RENAME @procname,@temp

print(@procname + ‘已被修改’)

fetch next from proccur into @procname

end

close proccur

deallocate proccur

删除：


复制代码 代码如下:

declare proccur cursor

for

select [name] from sysobjects where name like ‘Users_%’

declare @procname varchar(100)

open proccur

fetch next from proccur into @procname

while(@@FETCH_STATUS = 0)

begin

exec(‘drop proc ‘ + @procname)

print(@procname + ‘已被删除’)

fetch next from proccur into @procname

end

close proccur

deallocate proccur