Erik Darling, of Erik Darling Data (blog | Twitter) recently posted a very interesting video (my YouTube playlist is all Erik Darling Data videos and Honest Trailers). In this video he demonstrates an easily repeatable issue about naming weirdness and the
CREATE OR ALTER PROCEDURE syntax, with stored procedures starting with
sp_. The video is 141 seconds long, so go watch it. I’ll wait.
Cool. As many of my readers know — because you’re intelligent and attractive in equal measure — Microsoft does not want us using
sp_ for stored procedure prefixes for general use, because it’s kind of reserved:
Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name.
Of course our community likes to release super amazing stored procedures like sp_WhoIsActive and sp_Blitz and sp_HumanEvents, so this isn’t a hard and fast rule.
Create or alter
You can quite easily create a stored procedure with the prefix, both in the master database and any user databases you have, using
CREATE PROCEDURE. However what Erik shows is that if you already have a stored procedure with a name that has the
sp_ prefix in the master database, and then use
CREATE OR ALTER syntax to create a stored procedure in a user database with the same name, you’ll get an “Invalid object name” error in the user database:
Msg 208, Level 16, State 6, Procedure sp_DoYouEvenLiftBro, Line 1 [Batch Start Line 18] Invalid object name 'dbo.sp_DoYouEvenLiftBro'.
So far, so good. I’ve summarised in 200 words what Erik showed in his video. Then again, this is an edge case and not a typical situation someone would find themselves in, because:
- You shouldn’t be creating regular stored procedure names with the
sp_prefix in the first place;
- You shouldn’t have objects with the same name in the master database and a user database because that’s just confusing.
Except this is not entirely true. If you run the
CREATE OR ALTER statement on the user database a second time, it will execute successfully. The “OR ALTER” part works just fine. It seems to get hung up with the “CREATE” part, but not
CREATE on its own.
Stay with me folks, because it’s about to get weirder.
I happen to know of an undocumented system stored procedure called
sys.sp_MS_marksystemobject, which can mark a database object (by convention in the master database) as a system object, and therefore accessible from any user context without needing the three-part name. So what if I mark the master database version of the stored procedure as a system object? I’m so glad you asked, invisible question-asker. The answer is, there’s no error at all in the user database. If the stored procedure with the same name already exists in the master database, but is marked as a system object, then you can quite easily create a stored procedure with that name in a user database, using
CREATE OR ALTER.
Summarise this for me
- Don’t create stored procedures with an
- If you do, don’t create stored procedures with an
sp_prefix, with the same name, in the master and user databases.
- If you do, don’t use
CREATE OR ALTERto create the user database version of the stored procedure.
- If you do, you have to mark the master database version as a system object first or it will fail.
- But only on the CREATE part, not the ALTER part, because that works fine.
Got that? Good.
Script to play with
USE master; GO DROP PROCEDURE IF EXISTS dbo.sp_DoYouEvenLiftBro GO CREATE OR ALTER PROCEDURE dbo.sp_DoYouEvenLiftBro --CREATE PROCEDURE dbo.sp_DoYouEvenLiftBro AS SELECT 'If you''re not afraid of getting schvitzy' AS GiveYourLegsAnIncrediblePump GO --EXEC sys.sp_MS_marksystemobject 'sp_DoYouEvenLiftBro' --GO USE Test; GO DROP PROCEDURE IF EXISTS dbo.sp_DoYouEvenLiftBro GO CREATE OR ALTER PROCEDURE dbo.sp_DoYouEvenLiftBro --CREATE PROCEDURE dbo.sp_DoYouEvenLiftBro AS SELECT 'If you''re not afraid of getting schvitzy' AS GiveYourLegsAnIncrediblePump GO --EXEC sys.sp_MS_marksystemobject 'sp_DoYouEvenLiftBro' --GO
Leave your thoughts in the comments below.
Photo by Nathan Dumlao on Unsplash.
You had me at #1. All I need to know. But is is interesting. 🙂
Thank you! this is exactly the issue I had today. I could not figure out why I was getting the error. After reading your article, I checked the master database, and found that a whole bunch of user stored procs were created in master 4 months ago. Seems like someone deployed the code to master instead of the user database.