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.