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:
- The stored procedure must be created in the master database.
- The name of the stored procedure must start with “sp_“.
- 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.
-- 1. Create the procedure in the master database USE master GO -- 2. Create the procedure with the prefix sp_ CREATE PROCEDURE sp_[Stored_Procedure_Name] AS BEGIN -- Insert the logic of your stored procedure here END GO -- 3. Mark the stored procedure as a system object 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.
July 10, 2013 at 3:45 am
Its not necessary to use “sys.sp_MS_marksystemobject” procedure to make an user defined procedure available in all the DBs!
Once the procedure prefixed with “SP_” in Master DB, It’ll become available in all the DBs!
June 4, 2014 at 8:37 am
JasmineJ i tried in SQL 2005 and noticed it is necessary to use this statement EXEC sys.sp_MS_marksystemobject sp_[Stored_Procedure_Name] other wise we can not use that store proce referring to current database…
October 9, 2015 at 10:29 am
Very good, I needed this.
One suggestion — I changed your PRINT statements to RAISERROR(‘Message’, 0,1) WITH NOWAIT so that any status message would be instantly returned in SSMS.