删除特定SQL Server架构中的所有存储过程

问题描述：

我有数百个由DataSync自动生成的过程。删除特定SQL Server架构中的所有存储过程

我不必手动删除他们的时间和感觉。

他们都开始DataSync.

有没有办法删除所有存储过程，其中名称与DataSync.开始？

哪个RDBMS会变成这样吗？ – 2012-02-08 10:23:18

答

使用INFORMATION_SCHEMA.ROUTINES（这是跨RDBMS的相当标准，如MSSQL，MySQL的）：

如果您的proc名称开始“DataSync”。那么他们很可能是在一个模式，这样你就可以找到他们：

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

如果您PROC名称开头“数据同步”，那么你可以找到他们：

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

如果你想执行所有这些下降的语句，你可以建立一个执行使用FOR XML路径，如下所示：

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)

完美工作;-) – dg90 2012-02-08 10:34:11

答

假设你的意思SQL服务器当您指定“SQL” - 那么最简单的方法是：运行此查询：

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

和甚至“懒惰”的版本将使用游标自动为您做到这一点：

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

我想知道“。”在名字的结尾他说。也许他们都在一个模式。 – 2012-02-08 10:24:35

@JonEgerton：好点 - 用可选的第二种方法更新我的答案 – 2012-02-08 10:27:06

是的，他们在模式中 – dg90 2012-02-08 10:31:12

答

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

编辑：改变where子句因为它证明DataSync是一个模式名称。

由于这些存储过程是一个'DataSync'架构里面，这个代码将失败 - 在“名称”将像'storedproc1'但命令'DROP PROCEDURE storedproc1'会失败，因为你需要的** **架构添加到该存储过程的名称！ – 2012-02-08 10:35:00

@marc_s：感谢您指出了（这是不是在数据同步的问题是否是模式的原始版本明确） – Tomek 2012-02-08 10:50:44

两件事情：它仍然行不通的，因为你从系统目录视图获取'name'不包含模式 - 所以你的命令仍然是“DROP PROCEDURE proc1”，这是行不通的。另外：我会一直使用最具体的目录视图，例如'sys.procedures' - 这样，你甚至不需要指定要对象的类型 - 这是从你使用 – 2012-02-08 12:13:23

答

我一直在使用这个简短的脚本来清除给定模式所有SP（使用SQL Server时）。它迭代直接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

前提与marc_s提供的答案非常相似;但是不会为迭代使用游标。尽管sys.procedures中有与我们的模式相匹配的记录，但我们需要删除它。

答

无需XML或循环：

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)

删除特定SQL Server架构中的所有存储过程

相关推荐