Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating Your Own SQL Server System Stored Procedures


By:   |   Read Comments (7)   |   Related Tips: More > Stored Procedures


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


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
IF OBJECT_ID('SP_GETOBJECTS'IS NOT NULL
DROP PROCEDURE DBO.sp_GETOBJECTS
GO
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 table
SELECT *
FROM sys.objects
WHERE NAME 'Employee'

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

SELECT TOP *
FROM HumanResources.Employee
GO

USE AdventureWorks
GO
EXEC sp_GETOBJECTS
GO

Examining the output, we can see that the DB_NAME() function ran under the context of the AdventureWorks 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 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 specific catalog information.

Well, the SQL Server 2000 system tables will be eventually phased out so how do we get around this? Well, 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 NAMEIS_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.

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

USE AdventureWorks
GO
EXEC sp_GETOBJECTS
GO

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 in the SQL Server 2005 Books Online
  • If you have stored procedures in your system prefixed with sp_, consider renaming them to avoid performance penalties
  • Take caution if you're considering adding objects to the master database on a production server


Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

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

 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

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

 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

 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

 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


Learn more about SQL Server tools