solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Idera - SQL diagnostic manager

SQL diagnostic manager is a powerful performance monitoring and diagnostics solution that proactively alerts administrators to health, performance or availability problems within their SQL Server environment via a central console or mobile device. SQL diagnostic manager minimizes costly server downtime by providing agent-less, real-time monitoring and customizable alerting for fast diagnosis and remediation of SQL Server performance and availability problems. SQL diagnostic manager also provides a 'community' environment where, using Idera's IntelliFeed(TM) technology DBAs form a community to collaborate to quickly resolve problems, perform maintenance and capture best practices.

Learn more!

Creating Your Own SQL Server System Stored Procedures

By: Armando Prato | Read Comments (6) | Print

Armando has over 24 years of industry experience and has been working with SQL Server since version 6.5.

Related Tips: More

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

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

Related Tips: More | Become a paid author

Last Update: 10/24/2008

Share:

Free SQL Server Learning

Comments and Feedback:

Friday, October 24, 2008 - 7:19:23 AM - jerryhung

Read The Tip

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

Friday, October 24, 2008 - 7:48:33 AM - aprato

Read The Tip

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 - 8:39:28 AM - aprato	Read The Tip
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 - 9:10:16 AM - jerryhung

Read The Tip

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 - 10:46:44 AM - aprato	Read The Tip
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 - 10:53:46 AM - jerryhung

Read The Tip

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

USE master

create procedure Jerry AS

select GETDATE()

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

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!"

SQL Monitor – For database professionals who need results on Day One. Try it online.

What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check consulting services.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Join the over million SQL Server Professionals who get their issues resolved daily.

Are you waiting on SQL Server? Learn about these DMV's.