Delete all stored procedures in a specific SQL Ser

问题:

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

回答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)

回答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 

回答3:

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.

回答4:

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.

回答5:

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)

回答6:

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)