我们操作SQL数据库的时候,可能需要修改或删除存储过程,当工作量比较大的时候我们可以采用批量修改的方式,以节省时间,提高工作效率。本文以代码的形式来说明了这一过程。
修改:
- 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 '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
以上就是SQL数据库批量修改和删除存储过程的代码演示过程,如果想了解更多数据库的操作,请访问:http://database.51cto.com/。
【编辑推荐】