How stored procs with prefix sp_ behave

Caution!

This is part 1 of my series on sp_ behavior.

In SQL Server, when a stored procedure is created with prefix sp_ and deployed in master, it can be accessible from any database context, even though it is not present physically on any other user/system database. (Think of sp_whoisactive)

I’ll walk you through an example to understand this behavior.

1. Create a stored procedure sp_CheckMe in master database:

USE master 
GO
CREATE OR ALTER PROC dbo.sp_CheckMe
AS
BEGIN
SELECT 'I AM FROM master DATABASE'
END
GO

sp_CheckMe is deployed in master database now, let’s execute it.

USE master 
GO
EXEC dbo.sp_CheckMe

It returns results from master as expected.

2. Execute sp_CheckMe from StackOverflow2013 database, where it is not deployed yet.

USE StackOverflow2013
SELECT * FROM sys.procedures WHERE name ='sp_CheckMe'
EXEC dbo.sp_CheckMe

Here, database engine executed the stored procedure from master since it didn’t find sp_CheckMe in StackOverflow2013 database.
Reminder - since this stored proc starts with sp_ and is present in master, it can be accessed from any database in that instance, even though it is not present physically on any other user/system database.

3. Create sp_CheckMe in StackOverflow2013 database - same name but different content.

USE StackOverflow2013 
GO
CREATE OR ALTER PROC sp_CheckMe
AS
BEGIN
SELECT 'I AM FROM StackOverflow2013 DATABASE'
END
GO

4. Execute this on StackOverflow2013 database

USE StackOverflow2013 
GO
EXEC dbo.sp_CheckMe

It checked StackOverflow2013 for the existence and executed the store procedure from StackOverflow2013. It did not go to master, since the stored procedure is already available in the specified database context.

What did we see now:

For stored procedures that start with sp_, database engine

• first checks in the specified database context that you’re running from. If it is present, then it executes. If not,

• Then it checks in master database and executes.

• If it doesn’t exist in master then it fails.

Takeaways:

• If stored procedures starting with sp_ are created in master, they can be accessed from any database in the server.

• If stored procedures starting with sp_ are not created in master, then they can be accessed only from the database where it was created.

• Database engine first checks for stored procedure starting with sp_ in the database context you’re running from, if it is not present, then it checks in master database.

• For example, this is the reason sp_whoisactive is created in master, so it can be accessed from anywhere in the server. Other system stored procedures that can be created in master and have been very useful to me are sp_Backupserver by Ola Hallengren for backup solution and sp_helpExpandView by Andy Yun to list the tables/objects from (nested)views.

• Behavior is the same even when sp_ is marked as system object using

  EXEC sys.sp_ms_marksystemobject 'dbo.sp_CheckMe' 
  GO 

Caution:

Avoid creating stored procedures with prefix sp_ because

• It may not be intentional while developing stored procedure to execute from any database context.

• Stored procedures with sp_ takes a few milliseconds more during execution compared to stored procedures without prefix sp_. Post coming up soon with demo in part 2 of this article.

Conclusion:

Create stored procedures with prefix sp_ only if it is something you want it to be executed when called from any database context, such as sp_whoisactive, since it checks the specified database context and then the master database.
For user-defined stored procedures, create them with anything other than sp_ to avoid confusion and have improved runtime.

Resources:

Part-2: How dropping stored procs with prefix sp_ behave

Part-3: Performance of stored procs with prefix sp_ - coming soon.

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure?view=sql-server-ver16

Thanks for reading,
Haripriya