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.?

Solution 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)

Solution 2

Assuming you mean SQL Server when you specify "SQL" - then the easiest way is this: run this query:

SELECT 
    name, 
    DropCmd = 'DROP PROCEDURE DataSync.' + name 
FROM sys.procedures
WHERE 
     schema_id = SCHEMA_ID('DataSync')

and the even "lazier" version would be to use a cursor to do this automatically for you:

DECLARE DropSpCursor CURSOR FAST_FORWARD FOR
    SELECT 
        name
    FROM sys.procedures
    WHERE schema_id = SCHEMA_ID('DataSync')

DECLARE @StoredProcName sysname
DECLARE @DropStatement NVARCHAR(1000)

OPEN DropSpCursor

FETCH NEXT FROM DropSpCursor INTO @StoredProcName, @SchemaName

WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        SET @DropStatement = N'DROP PROCEDURE DataSync.' + @StoredProcName
        EXEC(@DropStatement)
    END

    FETCH NEXT FROM DropSpCursor INTO @StoredProcName
END

CLOSE DropSpCursor 
DEALLOCATE DropSpCursor 

Solution 3

No need for XML or loops:

declare @sql varchar(max) = ''

select @sql += 'drop procedure [' + routine_schema + '].[' + routine_name + '];'
from information_schema.routines where routine_schema = 'DataSync' and routine_type = 'PROCEDURE'

exec(@sql)