如何跨大量數據庫刪除過程（SQL Server 2016）

我有一個存在於 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，以便我可以在該數據庫的上下文中刪除過程。但它不起作用。我該怎麼辦？我被困在聰明的方法上，而且我現在比愚蠢的方法花了更多的時間。

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

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

引用自：https://dba.stackexchange.com/questions/255011