Drop group of stored procedures by name

Question

I have group of stored procedures with names like 'somename_%'. Are there any way to delete that SP with one query, forexample

DROP PROCEDURE where name like 'somename_%'

.

Answer 1

This works for MSSQL 2005 +

DECLARE @DropScript varchar(max)
set @DropScript = ''

SELECT @DropScript = @DropScript + 'DROP PROCEDURE [' + schema_name(schema_id)+ '].' + '[' + name + ']
' FROM sys.procedures
where name like 'somename_%'


exec (@DropScript)

Answer 2

You can generate the DDL by querying the data dictionary. For example, in Oracle:

SELECT 'DROP PROCEDURE "'||owner||'"."'||object_name||'";'
FROM all_procedures
WHERE procedure_name IS NULL
AND lower(object_name) LIKE 'somename_%';