我们操作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/。