Creating Your Own SQL Server System Stored Procedures

By:   |   Updated: 2018-08-30   |   Comments (7)   |   Related: > Stored Procedures


Problem

Microsoft provides a good set of basic queries into their system tables. However, some of these procedures lack some additional information that I'm interested in and I find myself constantly issuing queries to get this additional data. Is there a way I can run these queries like system procedures?

Solution

By creating a stored procedure in the SQL Server master database prefixed with sp_, you can take advantage of the engine's stored procedure name resolution algorithm. If a stored procedure begins with sp_, the engine will search the master database first before checking the current database and ownership. As an aside, this name resolution is precisely why you should never prefix your application database stored procedures with sp_ as you will continually incur the overhead associated with an unnecessary search and a cache miss every time you attempt to execute these procedures.

When running an sp_ prefixed procedure from master, there are some subtleties as to which database context is used, which impacts what data is returned.

Consider the following example:

USE MASTER 
GO

-- drop stored procedure if it exists
IF OBJECT_ID('SP_GETOBJECTS') IS NOT NULL 
DROP PROCEDURE DBO.sp_GETOBJECTS 
GO 

-- create stored procedure
CREATE PROCEDURE DBO.sp_GETOBJECTS 
AS 
   SET NOCOUNT ON 

   SELECT DB_NAME() 

   -- ANSI view 
   SELECT * 
   FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_NAME = 'Employee' 

   -- SQL Server 2005 and later table 
   SELECT * 
   FROM sys.objects 
   WHERE NAME = 'Employee' 

   -- SQL Server 2000 table 
   SELECT * 
   FROM sysobjects 
   WHERE NAME = 'Employee' 

   SELECT TOP 1 * 
   FROM HumanResources.Employee 
GO 

-- execute the stored procedure
USE AdventureWorks 
GO 

EXEC sp_GETOBJECTS 
GO 

Examining the output, we can see that the DB_NAME() function ran under the context of the AdventureWorks2017 database as did the SQL Server 2000 statement to retrieve the Employee table metadata from sysobjects. However, the equivalent INFORMATION_SCHEMA and SQL Server 2005 and later statements did not work and the SELECT from the Employee table failed outright. These did not work because the statements actually ran under the context of the master database. While the procedure was found in master, it wasn't smart enough to use the current database context for the SQL Server 2005 and later specific catalog information.

fig 1
fig 1a

Marking Stored Procedure as System Object

There's an undocumented system stored procedure named sp_ms_marksystemobject that can be used to flag the engine that the stored procedure should be run as though it were a Microsoft provided system stored procedure. Like any other of the undocumented commands, it could disappear in a future release and should be used at your own risk.

USE MASTER 
GO 

EXEC sp_ms_marksystemobject 'SP_GETOBJECTS' 
GO 

SELECT NAME, IS_MS_SHIPPED 
FROM SYS.OBJECTS 
WHERE NAME = 'SP_GETOBJECTS' 
GO 

Marking our procedure with sp_ms_marksystemobject shows that it has been registered with the engine as it were a Microsoft supplied procedure.

fig 2

Re-running the procedure now shows that all statements are run under context of the AdventureWorks database

USE AdventureWorks 
GO 

EXEC sp_GETOBJECTS 
GO 
fig 3

As you can see, this is a really effective way to centralize some of your frequently run queries so they're available for use against multiple databases hosted on your server. It should be noted that adding objects to the master database is not generally considered a good practice, so I don't make use of this feature on production servers. However, I do maintain several QA and Load Test servers and I make use of it in these cases.

Next Steps
  • Read more about the master database
  • Take caution if you're considering adding objects to the master database on a production server


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-08-30

Comments For This Article




Wednesday, July 10, 2013 - 5:31:07 AM - JasmineJ Back To Top (25773)

It seems, Procedure are prefixed with 'SP_' always search in Current/active database first! If its not there in current/active database then only goes to MASTER.

USE master
GO
Create Proc Sp_Test
as
begin
select 'MASTER' [Database]
end
go

USE Database1
GO
Create Proc Sp_Test
as
begin
select'DATABASE1' [Database]
end

Pls refer the link: http://www.dotnetfunda.com/articles/article1035-user-defined-stored-procedure-should-not-be-prefixed-with-sp.aspx

 


Friday, October 24, 2008 - 10:53:46 AM - jerryhung Back To Top (2083)

Never mind, I probably was in the 'master' db. sorry for the confusion

USE master

GO

create
procedure Jerry AS

select GETDATE()

GO

USE AdventureWorks2008

exec Jerry -- cannot find SP

exec master.dbo.Jerry -- only this works

USE MASTER

EXECUTE sp_ms_marksystemobject 'Jerry'

USE AdventureWorks2008

exec Jerry -- cannot find SP


Friday, October 24, 2008 - 10:46:44 AM - aprato Back To Top (2082)

 I tried your example on 2 separate SQL 2005 machines (Build 3042 and 3054) and neither resolved the name.  What build and version are you running?


Friday, October 24, 2008 - 9:10:16 AM - jerryhung Back To Top (2080)

Yep, I just tried that too, sucks

exec master.dbo.Jerry -- this works

USE MASTER

EXECUTE sp_ms_marksystemobject 'Jerry'

 

exec Jerry -- now this works after above statement

But it is interesting to read about this anyway, thanks

 

http://www.sqlservercentral.com/articles/Performance+Tuning/sp_performance/850/


Friday, October 24, 2008 - 8:39:28 AM - aprato Back To Top (2079)

 I just verified that you need to prefix with sp_ for this to work.  The name resolution does not kick in even if you mark a non sp_ prefixed proc.


Friday, October 24, 2008 - 7:48:33 AM - aprato Back To Top (2078)

 Hi Jerry

I'm not sure this would work.  I think it has to be prefixed with an sp_ to work.  I'll try it out on my test box and verify but I seem to recall that marking a proc that's not prefixed with sp_ didn't work because the name resolution does not kick in.


Friday, October 24, 2008 - 7:19:23 AM - jerryhung Back To Top (2077)

 I also believe if you create any SP in master DB, it can be called from any DB

I generally avoid naming anything sp_XXX just so I know it's not a MSFT SP. 

If you create a usp_XXX in master, it should be callable in any user DB as well and no confusion there















get free sql tips
agree to terms