Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

Running a master db residing stored procedure in current database context

I recently wanted to create a stored procedure that would be only in master database and get some information_schema stuff for the current database.

One requirement is that the sproc has s "sp_" prefix. I also thought that it's the only requirement. I was wrong not for the first nor the last time.

The sproc would always return me information schema data belonging to the master db.

The second thing you have to do is mark the sproc a system object.

In Sql Server 2000 we have to set the system marking to on with master.dbo.sp_MS_upd_sysobj_category

In Sql Server 2005 we have the sys.sp_MS_marksystemobject stored procedure that does the trick.

This is how it's done:

SQL Server 2000:

USE master
-- This turns the SS2k's system marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
go
CREATE PROC sp_TestMasterAccess
AS
SELECT    * 
FROM    information_schema.tables

– This turns the SS2k's system marking off EXEC master.dbo.sp_MS_upd_sysobj_category 2 GO EXEC sp_TestMasterAccess

go USE northwind EXEC sp_TestMasterAccess

go USE master DROP PROC sp_TestMasterAccess

SQL Server 2005:

USE master
go
CREATE PROC sp_TestMasterAccess
AS
SELECT    * 
FROM    information_schema.tables
GO
-- SS2k5 provides a stored procedure to mark the object as system
EXEC sys.sp_MS_marksystemobject sp_TestMasterAccess
GO
EXEC sp_TestMasterAccess

go USE AdventureWorks EXEC sp_TestMasterAccess

go USE master DROP PROC sp_TestMasterAccess

 

Enjoy!

Legacy Comments



2007-01-18
re: Running a master db residing stored procedure in current database context
Aha!
This explains som behavior I did not fully understand.

Cheers!
rockmoose

SAinCA
2007-01-22
re: Running a master db residing stored procedure in current database context
This is one of the most useful tips I've encountered and will save me proliferating a utility SP to all DB's in all environments - THANKS!!!

Mladen
2007-01-22
re: Running a master db residing stored procedure in current database context
You're welcome!

DBAdmin
2007-01-22
re: Running a master db residing stored procedure in current database context
The information_schema.tables reference forces the extra requirement. The "sp_" prefix is all that's necessary if referencing system tables for schema information.

Mladen
2007-01-22
re: Running a master db residing stored procedure in current database context
that's true if you call your information_schema views directly from the query.
try running this script like it is and then comment the lines that mark the sproc
as system object.
you'll see that when the sproc is marked it returns details from the master db first
and then from the northwind db.

without marking as system object it returns master db data from both calls.

DBAdmin
2007-01-22
re: Running a master db residing stored procedure in current database context
I'm not following your "if" qualification to my post. When referencing sysobjects to get a list of tables there's no need to use an undocumented stored procedure to mark anything as a system object. The reference of information_schema.tables forces that requirement.

Mladen
2007-01-22
re: Running a master db residing stored procedure in current database context
i used Information schema views just as an example to show the difference in result of the marking as system object.

a good example of use here is a sp_SearchDB which searches the whole database for
a string.
by placing it in master and marking it as system object you can call it from any db in that db's context. if you don't mark it as system object the sproc will always have the context of master db.

any clearer? :))

Rick
2007-01-22
re: Running a master db residing stored procedure in current database context
Interesting tip, and definitely useful for some of the meta-procs I write. I do, however, agree with DBAdmin, that the "sp_" prefix is all that's required for many tasks. If you change "information_schema.tables" to simply "sys.sysobjects" in your script, you'll notice that it suddenly pulls out only those objects in the current db.

This is probably related to the way SQL Server 2005 introduced schema support; perhaps a schema prefix implies a database ownership, and only specific compatibility views remain aloof of such restrictions?

To reproduce your query results without flagging the stored proc as a system object, replace the query in the stored proc with the following (I found adding an "order by" on the name column makes it easier to verify the results.)


SELECT *
FROM sys.sysobjects
where xtype = 'U'
order by name

Mladen
2007-01-23
re: Running a master db residing stored procedure in current database context
That is of course true.
However, using information_schema views is preffered since they aren't planned to change in the next releases and sys* everything is.
just look at the diff between SS2k and SS2k5 ones.



DBAdmin
2007-01-23
re: Running a master db residing stored procedure in current database context
"That is of course true."

You now make it sound as though you thoroughly understood this all along, but you seemed totally oblivious to it when I first brought it up.

You appear to be concerned about what could change between releases, but you have no problem relying upon an undocumented Microsoft stored procedure. Does that make any sense?

Mladen
2007-01-23
re: Running a master db residing stored procedure in current database context
totally oblivious?
maybe you should reread once more.

i know what constraints information_schema views have.
those constraints exist when used directly as a statement in query not in sproc.
if run from the sproc it uses the context of the sproc's db.

i'm not concerned about changes between releases.
it's simply preffered by MS to use information_schema views.
you can use whatever you like for all i care...

i really don't understand where do you see a problem?

and more information is never a bad thing. lighten up :))

DBAdmin
2007-01-23
re: Running a master db residing stored procedure in current database context
"However, using information_schema views is preffered since they aren't planned to change in the next releases and sys* everything is."

"i'm not concerned about changes between releases."

You made both of the statements above, but they are quite contradictory.

"maybe you should reread"

Maybe *you* should reread what *you* have written. You gave no indication that you understood the use of system tables. You simply went on about *how* to use information_schema views, as though they were the only option when another option had been suggested.

Mladen
2007-01-23
re: Running a master db residing stored procedure in current database context
"You made both of the statements above, but they are quite contradictory."
yes but they're also in different context.

sure... another option is a good thing.
thanx for that.

my use of information_schema views was to show the principle of system object marking
and that was it.
sys* had nothing to do with it.

Rick
2007-01-24
re: Running a master db residing stored procedure in current database context
Whoa guys, no need to get upset over a simple tip. For the record, I agree with both of you on different points:

- It's quite useful to be able to mark objects as system objects, especially if that's the only way (short of mixing DB_NAME() with dynamic sql) to query information_schema views from the current context.

- The point of the Information_Schema is actually to provide SQL92 compliant catalogue views, which let you port your code to other SQL92 RDMSs. The sys.* schema is the root of the information for the information_schema.* views. You're right that sys* and sys.sys* compatibility views are frowned upon, just be aware that the rest of the sys.* objects will be around for a few more versions.

Of interest, it seems the reason that the new views like "sys.objects" are run in the context of the home database rather than the calling database (like "sys.sysobjects") is that the new views reside on a new read-only Resource database. The compatibility views reside there as well, but actually call back to the old system tables. Looks like sp_MS_marksystemobject may also be located in the Resource database. I wonder if there's a plan to include a context flag, where we can specify that a reference should be retrieved from the calling context?

Mladen
2007-01-24
re: Running a master db residing stored procedure in current database context
nah we're not upset :)
we're just exchanging views and opinions...


"new read-only Resource database"
rick which one is that? and where so you find it?
My SS2k5 has the standard 4 system tables (master, model, tempdb, msdb)

Jeremy
2007-01-25
re: Running a master db residing stored procedure in current database context
Thanks - tip was helpful...

Truett Woods
2007-02-27
re: Running a master db residing stored procedure in current database context
I found this while performing a google search. I have a dbDBA database that has
many useful generic procs & functions, some of which, I'd like to be able to run
in the scope of other databases without having to add them to each database.

Is there a way to flag them to run in the current db scope without adding them to
the master database? It's considered bad form to add procs/funcs to master.

Thanks in advance.

Mladen
2007-02-28
re: Running a master db residing stored procedure in current database context
not that i know of.

Mladen
2007-10-08
re: Running a master db residing stored procedure in current database context
thanx for the info clifford. appreciated!

Jason
2007-11-07
re: Running a master db residing stored procedure in current database context
Once a SP is marked as system, how to you get it unmarked?/

Mladen
2007-11-07
re: Running a master db residing stored procedure in current database context
in ss2k5 just drop it and recreate it.

Mladen
2007-11-15
re: Running a master db residing stored procedure in current database context
do you have it marked as a system object?
beacuse for the tables that aren't in every db the scope of search is still the parent databse of the stored procedure, whihc in this case is master db.

Aaron
2007-12-20
re: Running a master db residing stored procedure in current database context
I've been racking my brain trying to figure out why I kept getting Master DB info instead of the current. Thanks SO MUCH!!

Lino Barreca
2009-06-18
re: Running a master db residing stored procedure in current database context
UR DA MAN!!!

THANK YOU SO MUCH! :D

The most useful SQL tip I found so far on the internet!

DBNewbie2007
2009-12-15
re: Running a master db residing stored procedure in current database context
I agree that anyone trying to use undocumented procedures (like sys.sp_MS_marksystemobject) are asking for trouble. There is a excellent blog that discusses system objects which should be read prior to using the sp_MS_Marksystemobject procedure:

http://sqlblog.com/blogs/kalen_delaney/archive/2008/08/10/geek-city-system-objects.aspx


Srinu
2010-01-22
re: Running a master db residing stored procedure in current database context
Excellent. This solved my requirement