This little piece of code dumps each user-defined stored procedure one by one as iterates through them all (system procedures excluded). Note: You should not run the attached code snippet in production environments as it is a risky script. Every user-defined stored procedure in the database is iterated through and dropped one at a time. Serious repercussions may result from this, particularly if your database contains mission-critical processes.

declare @procName varchar(500)
declare cur cursor

for
select [name] from sys.objects
 where type = 'p' and  is_ms_shipped = 0

open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    exec('drop procedure [' + @procName + ']')
    fetch next from cur into @procName
end
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.