Accessing system views in global stored procedures

Having utility functions that do helpful stuff is great, just look at sp_who2, sp_help, sp_helpindex as examples.

What if you want to write your own. Well you can you just stick the procedure in master and prefix it with sp. You can now call the procedure from any database without calling master..procedure.

The real worth of these comes when you access system views like sys.objects and sys.tables. However unfortunately these are scoped to the database of the procedure and NOT the context database. This is a change from the previous system views in SQL 2000 and before. With those you can reference them in a global stored proc and they would assume the context of the database they are called from.

To show you the behaviour run the following code. It creates a stored proc in the master database for use elsewhere. It accesses sys.objects and sysobjects to show the difference in behaviour.

You will see that the sys.objetcs query always returns the count of objects from master. whereas the count of sysobjects returns the current database sysobjects count.

use master

go

drop procedure sp_getObjectCount

go

create procedure sp_getObjectCount

as

select db_name(), 'sys.objects' sysview ,COUNT(1) from sys.objects

select db_name(), 'sysobjects' sysview ,COUNT(1) from sysobjects

go

select db_name(), 'sys.objects' sysview ,COUNT(1) from sys.objects

go

exec master..sp_getObjectCount

go

exec msdb..sp_getObjectCount

go

use msdb

exec sp_getObjectCount

go

 

How do you get the best of both worlds well the only way I know is to use the undocumented procedure sp_MS_MarksystemObject This flags the procedure to behave how you expect.

 

Note: THIS IS AN UNDOCUMENTED PROCEDURE SO USE WITH CAUTION

 

use master

exec sp_MS_MarksystemObject 'sp_getObjectCount'

go

exec msdb..sp_getObjectCount

When you execute the procedure you know get the same results.


Published Wednesday, February 24, 2010 3:47 PM by simonsabin
Filed under: ,

Comments

Thursday, February 25, 2010 3:56 AM by SqlServerKudos

# Accessing system views in global stored procedures

Kudos for a great Sql Server article - Trackback from SqlServerKudos

# 101 on jQuery Selector Performance | JAVA Developer's Journal | Java WebDev Insider

Pingback from  101 on jQuery Selector Performance | JAVA Developer's Journal | Java WebDev Insider