sqlserver 批量删除存储过程和批量修改存储过程的语句

在 SQL Server 中，可以使用 T-SQL 语句批量删除存储过程和批量修改存储过程。以下是 SQL Server 批量删除存储过程和批量修改存储过程的完整攻略，包含了详细的步骤和两个示例说明：

1. 批量删除存储过程

可以使用以下 T-SQL 语句批量删除存储过程：

DECLARE @name VARCHAR(128)
DECLARE @sql VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @sql = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@sql)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END

以上 T-SQL 语句会循环遍历数据库中的所有存储过程，并逐个删除。执行该语句前需要注意备份数据，以免误删数据。

2. 批量修改存储过程

可以使用以下 T-SQL 语句批量修改存储过程：

DECLARE @name VARCHAR(128)
DECLARE @sql VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @sql = 'ALTER PROCEDURE [dbo].[' + RTRIM(@name) +'] AS BEGIN /* Your code here */ END'
    EXEC (@sql)
    PRINT 'Modified Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END

以上 T-SQL 语句会循环遍历数据库中的所有存储过程，并逐个修改。在 BEGIN 和 END 之间可以编写自己的代码。执行该语句前需要注意备份数据，以免误删数据。

示例说明

以下是批量删除存储过程的示例：

DECLARE @name VARCHAR(128)
DECLARE @sql VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @sql = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@sql)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END

以上示例会循环遍历数据库中的所有存储过程，并逐个删除。

以下是批量修改存储过程的示例：

DECLARE @name VARCHAR(128)
DECLARE @sql VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @sql = 'ALTER PROCEDURE [dbo].[' + RTRIM(@name) +'] AS BEGIN PRINT ''Hello, World!'' END'
    EXEC (@sql)
    PRINT 'Modified Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END

以上示例会循环遍历数据库中的所有存储过程，并逐个修改为输出 "Hello, World!" 的代码。