如何一次删除 SQLServer 数据库中的所有存储过程？

目前，我们对脚本文件中的每个存储过程使用单独的 drop 语句:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MySP]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MySP]

有没有办法把它们一次性扔掉，或者循环扔掉？

150444 次浏览
小开
最佳答案

类似(在 使用 SQLServer 中的存储过程从数据库中删除所有过程发现)。

顺便说一句，这似乎是一个 非常危险的事情做，只是一个想法..。

declare @procName varchar(500)
declare cur cursor


for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop procedure [' + @procName + ']')
fetch next from cur into @procName
end
close cur
deallocate cur
小开

我宁愿这样做:

  • 首先通过检查系统目录视图生成要删除的存储过程列表:

    SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + '];'
FROM sys.procedures p

    这将在 SSMS 输出窗口中生成一个 DROP PROCEDURE语句列表。

  • 将该列表复制到一个新的查询窗口中，并可能对其进行调整/更改，然后执行它

没有凌乱和缓慢的光标，让您能够检查和复查您的过程清单，要删除之前，您实际上删除它

小开

在 db 中创建下面的存储过程(db 要从中删除 sp)

然后右键单击该过程-单击“执行存储过程”。

然后点击确定。

create Procedure [dbo].[DeleteAllProcedures]
As
declare @schemaName varchar(500)
declare @procName varchar(500)
declare cur cursor
for select s.Name, p.Name from sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
WHERE p.type = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
ORDER BY s.Name, p.Name
open cur


fetch next from cur into @schemaName,@procName
while @@fetch_status = 0
begin
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @schemaName + '.' + @procName)
fetch next from cur into @schemaName,@procName
end
close cur
deallocate cur
小开
  1. 在“对象资源管理器”窗格中，选择“存储过程”文件夹。
  2. 按 F7(或从主菜单中选择 View > Object Explorer Details)。
  3. 选择所有程序 除了系统表。
  4. 按删除按钮并选择确定。

可以以同样的方式删除表或视图。

小开 
DECLARE @DeleteProcCommand NVARCHAR(500)


DECLARE Syntax_Cursor CURSOR
FOR
SELECT 'DROP PROCEDURE ' + p.NAME
FROM sys.procedures p


OPEN Syntax_Cursor


FETCH NEXT FROM Syntax_Cursor


INTO @DeleteProcCommand


WHILE (@@FETCH_STATUS = 0)
BEGIN


EXEC (@DeleteProcCommand)


FETCH NEXT FROM Syntax_Cursor
INTO @DeleteProcCommand


END


CLOSE Syntax_Cursor


DEALLOCATE Syntax_Cursor
小开

我认为这是最简单的方法:

DECLARE @sql VARCHAR(MAX)='';


SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects
WHERE type = 'p' AND  is_ms_shipped = 0


exec(@sql);
小开

试试这个:

DECLARE @sql NVARCHAR(MAX) = N'';


SELECT @sql += N'DROP PROCEDURE dbo.'
+ QUOTENAME(name) + ';
' FROM sys.procedures
WHERE name LIKE N'spname%'
AND SCHEMA_NAME(schema_id) = N'dbo';


EXEC sp_executesql @sql;
小开 
DECLARE @sql VARCHAR(MAX)
SET @sql=''
SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects
WHERE type = 'p' AND  is_ms_shipped = 0
exec(@sql);
小开

ANSI 兼容，无游标

DECLARE @SQL national character varying(MAX)
SET @SQL= ''


SELECT @SQL= @SQL+ N'DROP PROCEDURE "' + REPLACE(SPECIFIC_SCHEMA, N'"', N'""') + N'"."' + REPLACE(SPECIFIC_NAME, N'"', N'""') + N'"; '
FROM INFORMATION_SCHEMA.ROUTINES


WHERE (1=1)
AND ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_NAME NOT IN
(
'dt_adduserobject'
,'dt_droppropertiesbyid'
,'dt_dropuserobjectbyid'
,'dt_generateansiname'
,'dt_getobjwithprop'
,'dt_getobjwithprop_u'
,'dt_getpropertiesbyid'
,'dt_getpropertiesbyid_u'
,'dt_setpropertybyid'
,'dt_setpropertybyid_u'
,'dt_verstamp006'
,'dt_verstamp007'


,'sp_helpdiagrams'
,'sp_creatediagram'
,'sp_alterdiagram'
,'sp_renamediagram'
,'sp_dropdiagram'


,'sp_helpdiagramdefinition'
,'fn_diagramobjects'
,'sp_upgraddiagrams'
)




ORDER BY SPECIFIC_NAME




-- PRINT @SQL
EXEC(@SQL)

没有游标，不符合 ANSI 标准:

DECLARE @sql NVARCHAR(MAX) = N''
, @lineFeed NVARCHAR(2) = CHAR(13) + CHAR(10) ;


SELECT @sql = @sql + N'DROP PROCEDURE ' + QUOTENAME(SPECIFIC_SCHEMA) + N'.' + QUOTENAME(SPECIFIC_NAME) + N';' + @lineFeed
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
-- AND SPECIFIC_NAME LIKE 'sp[_]RPT[_]%'




AND ROUTINE_NAME NOT IN
(
SELECT name FROM sys.procedures WHERE is_ms_shipped <> 0
)




ORDER BY SPECIFIC_NAME




-- PRINT @sql
EXECUTE(@sql)
小开

通过混合光标和系统过程，我们将得到一个优化的解决方案，如下所示:

DECLARE DelAllProcedures CURSOR
FOR
SELECT name AS procedure_name
FROM sys.procedures;
OPEN DelAllProcedures
DECLARE @ProcName VARCHAR(100)
FETCH NEXT
FROM DelAllProcedures
INTO @ProcName
WHILE @@FETCH_STATUS!=-1
BEGIN
DECLARE @command VARCHAR(100)
SET @command=''
SET @command=@command+'DROP PROCEDURE '+@ProcName
--DROP PROCEDURE  @ProcName
EXECUTE (@command)
FETCH NEXT
FROM DelAllProcedures
INTO @ProcName
END
CLOSE DelAllProcedures
DEALLOCATE DelAllProcedures
小开

ANSI 兼容，无游标

PRINT ('1.a. Delete stored procedures ' + CONVERT( VARCHAR(19), GETDATE(), 121));
GO
DECLARE @procedure NVARCHAR(max)
DECLARE @n CHAR(1)
SET @n = CHAR(10)
SELECT @procedure = isnull( @procedure + @n, '' ) +
'DROP PROCEDURE [' + schema_name(schema_id) + '].[' + name + ']'
FROM sys.procedures


EXEC sp_executesql @procedure
PRINT ('1.b. Stored procedures deleted ' + CONVERT( VARCHAR(19), GETDATE(), 121));
GO
小开

获取数据库中所有存储过程的删除语句 选择’删除程序’+’’ + F.NAME +’; 对象作为 F，其中 type =’P’

小开

试试这个:

declare @procName varchar(500)
declare cur cursor


for SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'
FROM sys.procedures p
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec( @procName )
fetch next from cur into @procName
end
close cur
deallocate cur
小开

试试这个，对我有用

DECLARE @spname sysname;
DECLARE SPCursor CURSOR FOR
SELECT SCHEMA_NAME(schema_id) + '.' + name
FROM sys.objects
WHERE type = 'P';
OPEN SPCursor;
FETCH NEXT FROM SPCursor INTO @spname;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DROP PROCEDURE ' + @spname);
FETCH NEXT FROM SPCursor INTO @spname;
END
CLOSE SPCursor;
DEALLOCATE SPCursor;

提交答案