Create a Stored Procedure Available to All Databases

I needed to create a stored procedure today that could be used on any database within a given SQL Server instance. After some digging I found that there are three requirements for such a stored procedure:

1. The stored procedure must be created in the master database.
2. The name of the stored procedure must start with “sp_“.
3. The stored procedure must be marked as a system object.

The first two are quite easily accomplished but the third requirement requires the use of an undocumented stored procedure named sys.sp_MS_marksystemobject which will mark the created stored procedure as a system object.

01	-- 1. Create the procedure in the master database

02	USE master

03

GO

04

05	-- 2. Create the procedure with the prefix sp_

06	CREATE PROCEDURE sp_[Stored_Procedure_Name]

07

AS

08

BEGIN

09	-- Insert the logic of your stored procedure here

10

END

11

GO

12

13	-- 3. Mark the stored procedure as a system object

14	EXEC sys.sp_MS_marksystemobject sp_[Stored_Procedure_Name]

After you complete the above three steps, you can run the stored procedure on any of the databases found in that instance of SQL Server.

Note: If you need to “unmark” the procedure as a system object just simply drop the procedure and recreate it.