[SOLVED] Drop group of stored procedures by name

Drop group of stored procedures by name

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_%'

Solution

On recent versions you can use STRING_AGG

DECLARE @DropScript nvarchar(max)

SELECT @DropScript = STRING_AGG('DROP PROCEDURE ' + QUOTENAME(schema_name(schema_id)) + '.' + QUOTENAME(name) + ';', CHAR(10))
FROM sys.procedures
WHERE name like 'somename_%'

PRINT @DropScript

--Uncomment the line below if you are happy with the PRINT output and want the script executed
--EXEC (@DropScript)

On older versions you can use the following in place of STRING_AGG to get the script to execute

SET @DropScript = 
(
SELECT 'DROP PROCEDURE ' + QUOTENAME(schema_name(schema_id)) + '.' + QUOTENAME(name) + ';
'
FROM sys.procedures
WHERE name like 'somename_%'
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')