Drop Procedure with bad name (MSSQL)

user_863885 0
Viewed 58

I messed up and used sp_rename incorrectly and now I can't drop the procedure that was "created".

What I did was...

EXEC sp_rename '[schema].[OldSPName]','[schema].[OldSPName_TMP]'

What I expected was to get a new procedure like this 'schema.OldSPName_TMP' but what I got was 'schema.[schema].[OldSPName_TMP]'. So I'm trying to drop it but it's giving me the error that...

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

I was reading that using the schema as part of the new name in sp_rename would create a "buggy name", but I did not find anything about fixing it when you create the buggy name.

Anyone know how to drop this procedure?

sql-server sql
2 Answers

Using ":

DROP PROCEDURE schema."[schema].[OldSPName_TMP]";

db<>fiddle demo

user_50708790

You can just rename it correctly

EXEC sp_rename '[schema].[[schema]].[OldSPName_TMP]]]', 'OldSPName_TMP', 'OBJECT';

Note that the second parameter should be just the name, with no schema and no brackets. This was the mistake you made the first time.

user_148689970
Related