Query to Find and Drop User-Defined Stored Procedure

This code snippet iterates through all user-defined stored procedures (excluding system procedures) and drops them one by one.

Note. The provided code snippet is a dangerous script that should not be run in production environments. It iterates through all user-defined stored procedures in the database and drops them one by one. This can have serious consequences, especially if you have mission-critical procedures in your database.

declare @procName varchar(500) ,@type varchar(20)

declare cur cursor
 select [name],type from sys.objects where type  in ('P','V','FN','TF','IF') and  is_ms_shipped = 0 
 select [name],'TT' from sys.types where is_user_defined = 1
order by  type,name

open cur
fetch next from cur into @procName ,@type
while @@fetch_status = 0
	if @type = 'P'
		 exec('drop procedure [' + @procName + ']')
    else if @type = 'V'
		 exec('drop view [' + @procName + ']')
	else if @type in ( 'FN','TF','IF')
		 exec('drop function [' + @procName + ']')	
	else if @type = 'TT'
		 exec('drop TYPE [' + @procName + ']')

	print @procName +'   '+ @type

    fetch next from cur into @procName,@type
close cur
deallocate cur

Understanding the Code

The provided code demonstrates dropping user-defined stored procedures in a database. Here's a breakdown.

  • Variable declaration
    • @procName: String variable to store the name of each procedure.
    • cur: Cursor object to iterate through existing procedures.
  • Cursor definition:
    • Uses sys.objects system table to find user-defined procedures (type = 'p') excluding system procedures (is_ms_shipped = 0).
  • Looping through procedures
    • fetch next retrieves the next procedure's name into @procName.
    • while @@fetch_status = 0 repeats the loop until no more procedures are found.
  • Dropping procedures
    • Constructs a dynamic SQL statement (exec) to drop the current procedure (@procName).
    • Executes the dynamic statement.
  • Cleanup
    • Closes and deallocates the cursor to free resources.

Similar Articles