SQL: Making 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.


-- 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.

About these ads
Posted in SQL. Tags: . 1 Comment »

One Response to “SQL: Making a Stored Procedure Available to All Databases”

  1. JasmineJ Says:

    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!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: