How dropping stored procedures with prefix sp_ behave

This is part 2 of my series on sp_ behavior.

In the previous post part-1, we saw the behavior of stored procedures with sp_ prefix. In this post, let's dive into how dropping these stored procedures behaves.

Demo:

Scenario: dbo.sp_CheckMe is present in both master and StackOverflow2013 database.

Step 1: Drop dbo.sp_CheckMe from StackOverflow2013 database.
Query below checks for dbo.sp_CheckMe existence in both StackOverflow2013 and master. Then drops from StackOverflow2013.

SELECT * FROM StackOverflow2013.sys.procedures WHERE name='sp_CheckMe';
SELECT * FROM master.sys.procedures WHERE name='sp_CheckMe';
USE StackOverflow2013
GO
DROP PROCEDURE IF EXISTS dbo.sp_CheckMe
GO
SELECT * FROM StackOverflow2013.sys.procedures WHERE name='sp_CheckMe';
SELECT * FROM master.sys.procedures WHERE name='sp_CheckMe';

Result - The stored procedure is successfully dropped from StackOverflow2013 database as expected. It remains intact in the master database.

Step 2: Repeat step 1 - Drop dbo.sp_CheckMe from StackOverflow2013 database.
We know that this SP is deleted from StackOverflow2013 database in step 1. But, when executed, it does NOT error out as ‘invalid object name’.
Instead, it ends up checking in master database and drops dbo.sp_CheckMe from master. This is because of "sp_" prefix.

If you happen to drop from user database and if sp_ doesn’t exist, then it gets deleted from master. Even if you run - DROP PROCEDURE IF EXISTS , to check its existence in any user database, it still drops it from master database.
Be cautious when stored procedures with sp_ are dropped.
In the first place, they should be created only in master, if at all there is a need.
Any other stored procedures in user databases, may be created with usp_ or application name as prefix or with no prefix.

Takeaways:

When a stored procedure has the prefix "sp_":

• If you drop it under a database context other than "master":

  • It first checks for the procedure in the specified database.

  • If found, it will be dropped from that database.

  • If not found, it checks the "master" database.

  • If present in the "master" database, it will be dropped from there.

• But if it is marked as system object using sys.sp_ms_marksystemobject, then it can be dropped from master only when explicitly dropped from master. If it is meant to be a system stored proc, then do mark it as one, since it gets more protected from getting deleted.

  EXEC sys.sp_ms_marksystemobject 'dbo.sp_CheckMe' 
  GO 

Resources:

Part 1: https://gohigh.substack.com/p/how-stored-procs-with-prefix-sp_

Thanks for reading,
Haripriya.