Published on: 02 Jan, 2014

How to delete all stored procedures with specific prefix in one go using while loop in SQL Server?

Tumblr Digg Google Plus

In some occasion, you may want to delete a list of stored procedures with the same prefix name. You can do it easily using a while loop. You can use the following script and run in your SQL Query Panel.

/*  Run this command in sql query panel, you just need to replace 'Prefix_SPName' to your prefix table' */
declare @procName varchar(500)
declare icursor cursor
for select [name] from sys.objects where type = 'p' and name like 'Prefix_SPName%'
open icursor
	fetch next from icursor into @procName
	while @@fetch_status = 0
	begin
		exec('drop procedure ' + @procName)
		fetch next from icursor into @procName
	end
close icursor
deallocate icursor
GO

You probably want to check our other article about deleting all tables by using while loop and prefix condition.

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.