主页 / dba / 问题 / 255011
Accepted
Saeed Neamati
Asked: 2019-12-07 02:33:00 +0800 CST

如何跨大量数据库删除过程（SQL Server 2016）

  • 772

我有一个存在于 150 多个数据库中的简单程序。我可以一个一个地删除它们，这是软件开发界最愚蠢的工作。或者我可以以某种方式在一个查询中动态删除它们，这很聪明但不起作用。

我试过了：

execute sp_msforeachdb 'drop procedure Usages'

我有：

无法删除过程“使用”，因为它不存在或您没有权限。

所以我想也许我为它创建了一个游标。于是我写道：

declare @command nvarchar(max) = '
drop procedure Usages
';
declare @databaseName nvarchar(100)
declare databasesCursor cursor for select [name] from sys.databases where database_id > 5
open databasesCursor
fetch next from databasesCursor into @databaseName
while @@fetch_status = 0
begin
    print @databaseName
    execute sp_executesql @command
    fetch next from databasesCursor into @databaseName
end
close databasesCursor
deallocate databasesCursor

再次，我得到了同样的信息。我想，也许它不会改变数据库的上下文，因此我在删除命令前加上给定数据库的名称，所以命令会变得类似于drop procedure [SomeDatabase].dbo.Usages，但后来我收到：

'DROP PROCEDURE' 不允许将数据库名称指定为对象名称的前缀。

因此，我想出动态执行use @databaseName，以便我可以在该数据库的上下文中删除过程。但它不起作用。我应该怎么办？我被困在聪明的方法上，而且我现在比愚蠢的方法花了更多的时间。

sql-server sql-server-2016

2 个回答

  1. Best Answer

    您非常接近，但忘记了一件小事：

    execute sp_msforeachdb 'USE [?] drop procedure Usages'

    使用 [?] 实际上会转到每个数据库，并在那里执行它，这应该可以解决您的问题。
    但是，您可以添加一些东西，首先检查该过程是否存在，然后再删除它，因为现在如果数据库中不存在该错误，您仍然会收到错误消息。

    编辑：一个更干净的版本是这样的：

    DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
USE [?]
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N''Usages'')
                    AND type IN ( N''P'', N''PC'' ) ) 
drop procedure Usages
'

EXEC sp_MSforeachdb @SQL

    它会在丢弃之前检查它是否存在，所以如果有这样的sp，它不会给你一个错误。

    EDIT2：ElliotMajor 发表了评论

    我会注意到 sp_msforeachdb 没有记录，不应该使用。大量已知错误

    他是对的。对于您现在正在做的事情，应该没问题，但是如果您需要将其放入一些生产代码中，则有几个替代品可以替代这个 sp。

    改进版本：Spaghetti DBA和Aaron Bertrand

    • 5

  2. 缺少一些信息，您使用的是什么版本的 SQL Server？你有什么权限？

    也就是说，我认为下面的代码应该可以工作，我已经在 SQL 2019 上对其进行了测试。

    这会为每个 drop 语句构建一个查询，然后遍历每个语句并执行它（如果失败则打印错误）

    如果您只想要语句，您可以将其更改为EXEC (@query)，PRINT @Query或者摆脱 LOOP 而只是SELECT * FROM #querytable

    GO

DECLARE @procedure_name SYSNAME,
        @query      NVARCHAR(MAX);

SELECT @procedure_name  = 'usages'

SELECT 'USE ' + QUOTENAME(NAME) +
       '; DROP PROCEDURE [dbo].' + QUOTENAME(@procedure_name) + ';' AS Query,
       0 AS Completed
INTO   #querytable
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE'

WHILE (SELECT COUNT(*) FROM #querytable WHERE Completed = 0) > 0
BEGIN
    SELECT TOP(1) @query = query FROM #querytable WHERE Completed = 0
    BEGIN TRY
        EXEC (@query)
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
    END CATCH
    UPDATE #querytable SET Completed = 1 WHERE Query = @query
END

DROP TABLE #querytable
    • 2

相关问题