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.
execute sp_msforeachdb 'drop procedure Usages'
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.