Making a Procedure Available in all Databases

If you’ve ever wondered how to make a procedure available in any database, it’s actually pretty simple. If you create a procedure in the master database with the prefix of “sp_”, it will be callable from any database.

I personally think this feature is great for utilities, however I would not use this for dependent objects. In other words, I would not advise creating a procedure (or function that is referenced by other procedures) within the master database. The biggest reason is that when restoring databases to other servers, you typically do not restore the master database. So it can be easily left out or forgotten. If that happens you may have a mission critical troubleshooting situation on your hands. However sometimes there may not be a way around it. But you can look into utilizing synonyms as an alternative.

Another interesting observation regarding placing procedures in the master database with the sp_ prefix is that the procedure is actually executed from the context of the master database. In other words, if you create a procedure to enumerate all the other procedures within your database and place it in master, once you execute the procedure from a database other than master, it will still enumerate the procedures in the master database.

Here’s an example:

After creating this procedure and calling it from AdventureWorks, it still enumerates all the procedures within the master database.

To get around this, we can use the undocumented procedure which will mark the procedure as a system object. Once we do that, the context of the call is from the database we are executing within.

The code is below:

[cc lang=”sql”]
USE MASTER
GO

CREATE PROCEDURE sp_routines
AS
BEGIN

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES

END
GO
EXECUTE sp_MS_marksystemobject ‘sp_routines’

USE AdventureWorks
GO

EXEC sp_routines
[/cc]