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?

CodePudding user response：

Using ":

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

db<>fiddle demo

CodePudding user response：

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.

CodePudding user response：

you can use drop procedure [schema].[[schema].[OldSPName_TMP]]