Quick Run-through with Bulk Drop Stored Procedures

Why bother with seeking optimized solution which you know that it is just one time deal? Let's use the CURSOR to perform loop and exec SQL one by one. It is long running task but efficient since it's never used again after that. Here is such a simple secret:

declare @name varchar(max)
declare dcursor CURSOR
FOR Select name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%' and name like 'xxx_%'

open dcursor
fetch next FROM dcursor
    INTO @name

while @@FETCH_STATUS = 0
BEGIN
    fetch next FROM dcursor
    INTO @name

    declare @sql nvarchar(max)
    SET @sql = N'drop procedure ' + @name
    SELECT @sql
    exec(@sql)
END
CLOSE dcursor
DEALLOCATE dcursor