How to drop a procedure across a lot of databases (SQL Server 2016)

Question

I have a simple procedure that exists in over 150 databases. I can delete them one by one, which is the most stupid work to do in the world of software development. Or I can somehow dynamically delete them all in one query, which is smart but doesn't work.

I've tried:

execute sp_msforeachdb 'drop procedure Usages'

I got:

Cannot drop the procedure 'Usages', because it does not exist or you do not have permission.

So I thought maybe I create a cursor for it. Thus I wrote:

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

Again, I got that same message. I thought, maybe it doesn't change the context of the database, thus I prefixed the delete command with the name of the given database, so commands would become similar to drop procedure [SomeDatabase].dbo.Usages, but then I received:

'DROP PROCEDURE' does not allow specifying the database name as a prefix to the object name.

So, I figured out to dynamically execute use @databaseName so that I can drop procedure in the context of that database. But it's not working. What should I do? I'm stuck at the smart method, and I've spent more time than the stupid method now.

Answer 1

You were very close, but forgot a small thing :

execute sp_msforeachdb 'USE [?] drop procedure Usages'

The use [?] will actually go to each db, and execute it there, this should solve your issue.
You could however add something that firsts checks if the procedure exists before dropping it, cause now you'll still get the error if it doesn't exists on the database.

Edit: A cleaner version would be this:

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

It'll check if it exists before dropping, so if there's so such sp, it won't give you an error.

EDIT2: ElliotMajor made the comment

I would note that sp_msforeachdb is undocumented and shouldn't be used. Plenty of known bugs

And he's right about that. For what you're doing right now it should be fine, but if you're ever in need to put this in some production code, there's a couple replacements for this sp.

Improved versions by :Spaghetti DBA and Aaron Bertrand

Answer 2

There's a bit of info missing, what version of SQL Server are you using? What permissions do you have?

That said I think the code below should work, I've tested it on SQL 2019.

This builds a query for each drop statement then runs through each and executes it (and prints the error if it fails)

You can change the EXEC (@query) to PRINT @Query if you just want the statements, or get rid of the LOOP and just 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