Add A Procedure to Master Database? Yes Please!

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

Crap!

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.

Who's the Master?

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

Success!

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.

Master Never Told a Lie

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!

© 2011 – 2012, John Morehouse. All rights reserved.

This entry was posted in T-SQL and tagged , , . Bookmark the permalink.

6 Responses to Add A Procedure to Master Database? Yes Please!

  1. Hi John,

    Thanks for sharing your analysis. It would be so cool if we could execute a stored procedure with a simple parameter to the affect of ‘do not change current db’; which is the default for sp_ procs. I have always refrained in principal from flagging something as a system procedure or even including it in a system area. A certain amount of trust is required. MS could just blow away that whole area with a new update.

    I have tried the alternative of passing in the db name, building the sql and executing it. Of course this is extremely inefficient with all but trivial procs.

    Spreading the stored proc to each user db is even more absurd.

    Chuck

  2. Hi Chuck!

    Thanks for the comment! I too usually refrained from flagging a procedure as a system procedure. I can’t even recall if I’ve ever done it for the very same reason that you mentioned. I don’t want to have to worry about Microsoft coming in with an upgrade and blowing something away. 😉

  3. Joe says:

    Hi guys,

    Thanks for this article and the discussion. I likewise am very hesitant to put things in the system area, let alone flagging them as system objects. Having said that – this is exactly the functionality I need in my current project. We are designing a system and separating each customer into their own database. I have some common data stored in a CentralDB. I also have several common stored procedures that need the ability to run in the context of each customer’s database. I really don’t want to mange a copy of those stored procedures in each database, so I am planning on using this approach.

    Having said all that, what drawbacks are there to this approach? I know MS can do whatever they want in master via service packs, etc. What other downfalls do you guys see? Do you use this in production systems?

    Thanks in advance

    Joe

  4. Hi Joe,

    Your caution is warranted but my experience indicates ‘go for it’. I maintain over a hundred db’s in a fairly stabilized production environment. If money isn’t a factor, you might look into Redgate which would update the procedures otherwise just make sure you use a unique naming system.

    As a transition system, I used MS-Access to update the procedures in every database. It probably could be done with VBscript as well. Despite numerous attempts to update the procedures with a Studio cursor based procedure, I always failed at changing the data base name when updating stored procedures.

    Best of luck,

    Chuck

  5. Hi Joe! Thanks for stopping by and for the comments!

    I would concur with Chuck in that while your caution is warranted, you can mitigate some of that by just being aware. If you know that you’ve got things marked as ‘system’ whenever you do an upgrade (CU or Service Pack) just have a script handy to go check to make sure things are still there, if not add them back. Assuming that you are not upgrading things daily, the ROI on having things easily available will most likely be greater than the cost of having to manage them if Microsoft wipes them out.

    I’ve also used SSIS to manage objects (both data & schema) in an environment so that’s option. Red Gate is also an excellent solution. I’ve used that heavily in my current environment and it’s always been a work horse. Another thought would be to include your procedure in Model such that any new databases would acquire the new procedure. That would help with new databases going forward and you could go back and retro-fit any existing databases. Of course, you potentially have the same issue of Microsoft coming in to wipe things out. If you also have ongoing development on the procedures, this would not help your cause and most likely would put you back to square one.

    We do not currently use this method of marking things as a system object in our environment. Instead, we have a utility database and all of our scripts are written to utilize third part naming so that calling i is just that easy. All of our servers have this utility database installed as part of the configuration process.

    Hope that helps!

  6. Pingback: Add A Stored Procedure to the SQL Server Master Database | Senior DBA

Leave a Reply