So while at last week’s SQL PASS Summit 2011, during the dinner on Friday night we had a discussion about adding stored procedures to the Master database and being able to refer to it anywhere in your SQL Server instance. This allowed for direct reference without a three part naming format, IE [database].[dbo].[procedure_name]
The discussion was primarily around whether or not you had to mark the stored procedure as a ‘system procedure’. Well, turns out that you don’t. However, after a little research I found out that by marking it as a ‘system procedure’, it will allow the procedure to run in the context of the database you are currently in versus running within the Master database, specifically when dealing with system objects.
Let’s take a look.
USE master GO IF (OBJECT_ID('dbo.sp_MyTest') IS NOT NULL) BEGIN DROP PROCEDURE dbo.sp_MyTest END GO IF (OBJECT_ID('dbo.usp_MyTest') IS NOT NULL) BEGIN DROP PROCEDURE dbo.sp_MyTest END GO -- create my procedure CREATE PROCEDURE [dbo].[sp_MyTest] AS BEGIN SELECT * FROM SYS.INDEXES END GO CREATE PROCEDURE [dbo].[usp_MyTest] AS BEGIN SELECT * FROM SYS.INDEXES END GO
So, now I’ve got two procedures in Master, one prefixed with ‘sp_’ and the other with a different prefix ‘usp_’. Neither of them has been marked as a ‘system object’ so let’s see what happens.
First we’ll change the database context to something other than Master, and then execute the two procedures to see what kind of results we get.
USE tempdb GO EXEC usp_MyTest GO EXEC sp_MyTest GO
As you can see from the error, the first procedure doesn’t work as it returns an error. this makes perfect sense right? We’ve changed the context of the connection and the engine can’t find the stored procedure named “usp_MyTest” in the current database. Let’s try the other query and see what happens.
The second does in fact return results, however you’ll see that it’s a list of indexes from the Master database.
Now, you’ll have to take my word for it that this is a list of indexes from the Master database. You can easily query sys.indexes to see that they are the same.
This also helps to prove that you can execute a procedure labeled as ‘sp_’, however notice that it’s still in the context of the ‘Master’ database even though we are currently in ‘TempDB’.
Let’s try marking it as a system object and see what that does to our procedure. Since we know that the ‘usp’ procedure isn’t going to work, I’m going to leave it along for now.
USE Master GO EXEC sp_MS_MarkSystemObject sp_MyTest GO
And now let’s run it again. Voila!
You’ll notice that the count is different as well as the index names. It’s a returning the list of indexes for the tempdb database. Again, it’ll be easy to prove, just query sys.indexes and you’ll see.
So, if you need to utilize a stored procedure that has been placed in Master for ease of use and you have to reference system tables within the database you are currently in, you’ll have to make sure to mark it as a system stored procedure.
As a minor side note, if you look at the sys.objects table in Master for our two stored procedures, you’ll notice that the ‘is_ms_shipped’ flag has been set to ‘1’ for our ‘sp_MyTest’ procedure. So, if you mark a procedure as a system object, you’ll be setting this bit to True.
Also, as far as I can tell, the only way to reset this flag is to drop the object and then recreate it. I suppose that you could update the sys.objects table, however updating a system table is never recommended.
Don’t forget that we need to clean up our mess. Master is one database that you would want to keep as lean and clean as possible.
USE master GO DROP PROCEDURE dbo.sp_MyTest GO DROP PROCEDURE dbo.usp_MyTest GO
There you have it!! Enjoy!