Sql-server – How to drop a procedure across a lot of databases (SQL Server 2016)

cursorssql serversql-server-2016stored-procedures

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.

Best Answer

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

Related Question