删除特定 SQL Server 架构中的所有存储过程 [英] Delete all stored procedures in a specific SQL Server schema

问题描述

我有数百个由 DataSync 自动生成的程序.

I have hundreds of procedures auto generated by DataSync.

我没有时间和感觉手动删除它们.

I don't have the time and sense to delete them manually.

它们都以 DataSync 开头.

有没有办法删除名称以DataSync.开头的所有存储过程?

Is there a way to delete all stored procedures where the name start with DataSync.?

推荐答案

使用 information_schema.routines(在 MSSQL、Mysql 等 RDBMS 中是相当标准的):

Use the information_schema.routines (which is fairly standard across RDBMSs such as MSSQL,Mysql):

如果您的 proc 名称以DataSync"开头.那么它们可能在一个架构中，因此您可以通过以下方式找到它们:

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'

如果您的 proc 名称以DataSync"开头，那么您可以通过以下方式找到它们:

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'

如果您想执行所有这些 drop 语句，您可以使用 FOR XML PATH 构建单个执行，如下所示:

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)

这篇关于删除特定 SQL Server 架构中的所有存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！