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

Which RDBMS would this be in?

Jon Egerton
– Jon Egerton

2012-02-08 10:23:18 +00:00
Commented Feb 8, 2012 at 10:23 — Jon Egerton
– Jon Egerton, Commented Feb 8, 2012 at 10:23

Jon Egerton · Accepted Answer · 2012-02-08 10:40:42Z

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)

marc_s · Accepted Answer · 2012-02-08 10:33:32Z

5

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

edited Feb 8, 2012 at 10:33

answered Feb 8, 2012 at 10:23

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

6 Comments

Jon Egerton Over a year ago

Am wondering about the "." at the end of the name stem he said. Maybe they're all in a schema.

marc_s Over a year ago

@JonEgerton: good point - updated my answer with an optional second way of doing this

dg90 Over a year ago

Yeah true, they are in a schema

Jon Egerton Over a year ago

I fancied using STUFF,ForXML and making a single executable statement of all the drops (just for fun like!)

marc_s Over a year ago

@JonEgerton: <hehe> seems like you have too much time on your hands!! :-)

|

Paul Holmes · Accepted Answer · 2017-03-22 11:01:13Z

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)

answered Mar 22, 2017 at 11:01

Paul Holmes

1626 bronze badges

Comments

Tomek · Accepted Answer · 2012-02-08 13:04:08Z

0

DECLARE @name  AS VARCHAR(max)

DECLARE MyCursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT name FROM sys.objects WHERE type='P' AND schema_id=SCHEMA_ID('DataSync')

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('DROP PROCEDURE DataSync.' + @name)
    FETCH NEXT FROM MyCursor INTO @name
END

CLOSE MyCursor
DEALLOCATE MyCursor

EDIT: changed where clause since it turned out DataSync is a schema name.

edited Feb 8, 2012 at 13:04

answered Feb 8, 2012 at 10:31

Tomek

3,2792 gold badges24 silver badges23 bronze badges

4 Comments

marc_s Over a year ago

Since those stored procedures are inside a DataSync schema, this code will fail - the "name" will be something like storedproc1 but the command DROP PROCEDURE storedproc1 will fail, since you need to add the schema to that sproc name!

Tomek Over a year ago

@marc_s: thanks for pointing (it wasn't clear in the original version of the question whether DataSync is a schema)

marc_s Over a year ago

Two things: it's still not going to work, since the name that you get from the system catalog views does not contain the schema - so your command still is DROP PROCEDURE proc1 and that won't work. Also: I would always use the most specific catalog view, e.g. sys.procedures - that way you don't even need to specify the type of object you want - that's obvious from the catalog view you use

Tomek Over a year ago

Thanks, good point. The second one, well a bit more efficient but functionally no difference.

RadicalCasey · Accepted Answer · 2014-05-26 01:31:11Z

I've been using this short script to clear all SPs from a given schema (when using SQL Server). It iterates direct sys.procedures.

DECLARE @schema VARCHAR(100)
SET @schema = 'DataSync'

DECLARE @CurrentStatement AS VARCHAR(MAX)
SET @CurrentStatement = (SELECT TOP(1)  'DROP PROCEDURE [' + @schema + '].[' + name + ']'
FROM sys.procedures 
WHERE schema_id = SCHEMA_ID(@schema))

WHILE @CurrentStatement IS NOT NULL
BEGIN
    EXEC (@CurrentStatement)    
    SET @CurrentStatement = (SELECT TOP(1)  'DROP PROCEDURE [' + @schema + '].[' + name + ']'
    FROM sys.procedures 
    WHERE schema_id = SCHEMA_ID(@schema))
END

The premise is very similar to the answer provided by marc_s; however doesn't utilize a cursor for the iteration. While there's a record in sys.procedures matching our schema, we need to delete it.

Haseeb · Accepted Answer · 2019-03-02 10:26:29Z

try this with sql2012 or above,

this will be help to delete all objects by selected schema keep 'P' and remove rest for stored procedure only (o.type IN ('P')

DECLARE @MySchemaName VARCHAR(50)='dbo', @sql VARCHAR(MAX)='';
DECLARE @SchemaName VARCHAR(255), @ObjectName VARCHAR(255), @ObjectType VARCHAR(255), @ObjectDesc VARCHAR(255), @Category INT;

DECLARE cur CURSOR FOR
    SELECT  (s.name)SchemaName, (o.name)ObjectName, (o.type)ObjectType,(o.type_desc)ObjectDesc,(so.category)Category
    FROM    sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    INNER JOIN sysobjects so ON so.name=o.name
    WHERE s.name = @MySchemaName
    AND so.category=0
    AND o.type IN ('P','PC','U','V','FN','IF','TF','FS','FT','PK','TT')

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category

SET @sql='';
WHILE @@FETCH_STATUS = 0 BEGIN    
    IF @ObjectType IN('FN', 'IF', 'TF', 'FS', 'FT') SET @sql=@sql+'Drop Function '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('V') SET @sql=@sql+'Drop View '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('P') SET @sql=@sql+'Drop Procedure '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('U') SET @sql=@sql+'Drop Table '+@MySchemaName+'.'+@ObjectName+CHAR(13)

    --PRINT @ObjectName + ' | ' + @ObjectType
    FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category
END
CLOSE cur;    
DEALLOCATE cur;
SET @sql=@sql+CASE WHEN LEN(@sql)>0 THEN 'Drop Schema '+@MySchemaName+CHAR(13) ELSE '' END
PRINT @sql
EXECUTE (@sql)

Collectives™ on Stack Overflow

Delete all stored procedures in a specific SQL Server schema

6 Answers 6

Comments

6 Comments

Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

6 Comments

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related