Delete all stored procedures in a specific SQL Server schema

Question

I have hundreds of procedures auto generated by DataSync.

I don't have the time and sense to delete them manually.

They all start with DataSync.

Is there a way to delete all stored procedures where the name start with DataSync.?

Answer 1

Use the information_schema.routines (which is fairly standard across RDBMSs such as MSSQL,Mysql):

If your proc names start "DataSync." then they are probably in a schema, so you can find them with:

select
    'DROP PROCEDURE [' + routine_schema + '].[' + routine_name + ']'
from 
    information_schema.routines where routine_schema = 'DataSync' and routine_type = 'PROCEDURE'

If your proc names start "DataSync" then you can find them with:

select
    'DROP PROCEDURE [' + routine_schema + '].[' + routine_name + ']'
from 
    information_schema.routines where routine_name like 'DataSync%' and routine_type = 'PROCEDURE'

If you wanted to execute all these drop statements, you can build a single execute using FOR XML PATH as follows:

declare @sql varchar(max)

set @sql = (
select
    'DROP PROCEDURE [' + routine_schema + '].[' + routine_name + '] ' 
from 
    information_schema.routines where routine_schema = 'DataSync' and routine_type = 'PROCEDURE'
FOR XML PATH ('')
)

exec (@sql)