I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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!

Print | posted on Thursday, January 18, 2007 5:25 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Running a master db residing stored procedure in current database context

Aha!
This explains som behavior I did not fully understand.

Cheers!
rockmoose
1/18/2007 6:53 PM |
Gravatar

# 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!!!
1/22/2007 6:42 PM | SAinCA
Gravatar

# re: Running a master db residing stored procedure in current database context

You're welcome!
1/22/2007 6:45 PM | Mladen
Gravatar

# 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.
1/22/2007 7:03 PM | DBAdmin
Gravatar

# 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.
1/22/2007 7:08 PM | Mladen
Gravatar

# 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.
1/22/2007 7:28 PM | DBAdmin
Gravatar

# 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? :))
1/22/2007 8:37 PM | Mladen
Gravatar

# 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
1/22/2007 9:42 PM | Rick
Gravatar

# 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.


1/23/2007 10:59 AM | Mladen
Gravatar

# 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?
1/23/2007 6:39 PM | DBAdmin
Gravatar

# 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 :))
1/23/2007 6:56 PM | Mladen
Gravatar

# 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.
1/23/2007 7:33 PM | DBAdmin
Gravatar

# 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.
1/23/2007 7:39 PM | Mladen
Gravatar

# 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?
1/24/2007 12:08 AM | Rick
Gravatar

# 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)
1/24/2007 10:37 AM | Mladen
Gravatar

# re: Running a master db residing stored procedure in current database context

Thanks - tip was helpful...
1/25/2007 11:50 PM | Jeremy
Gravatar

# 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.
2/27/2007 6:27 PM | Truett Woods
Gravatar

# re: Running a master db residing stored procedure in current database context

not that i know of.
2/28/2007 10:25 AM | Mladen
Gravatar

# re: Running a master db residing stored procedure in current database context

thanx for the info clifford. appreciated!
10/8/2007 7:53 AM | Mladen
Gravatar

# 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?/
11/7/2007 8:34 PM | Jason
Gravatar

# re: Running a master db residing stored procedure in current database context

in ss2k5 just drop it and recreate it.
11/7/2007 8:50 PM | Mladen
Gravatar

# 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.
11/15/2007 10:56 AM | Mladen
Gravatar

# 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!!
12/20/2007 2:51 PM | Aaron
Gravatar

# 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!
6/18/2009 10:39 AM | Lino Barreca
Gravatar

# 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

12/15/2009 7:44 PM | DBNewbie2007
Gravatar

# re: Running a master db residing stored procedure in current database context

Excellent. This solved my requirement
1/22/2010 5:41 PM | Srinu
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET