damn, really nice question, completely threw me. I was thinking about the fact that "sp_*" procs still need an extra property defined in order to act on objects (tables etc) that belong in the current database.

This sample illustrates what messed me up:

use master

go

create table LocalTable (testcolumn nVarChar(255))

go

INSERT INTO LocalTable SELECT 'This data is in master!'

go

create proc sp_test as

select db_name() AS DBNameFromFunction,

(select count(*) from sysobjects) AS ObjectCountFromSystemView,

(SELECT TOP 1 testcolumn FROM LocalTable) AS DBNameFromLocalObject

go

create database testdb

go

use testdb

go

create table LocalTable (testcolumn nVarChar(255))

go

INSERT INTO LocalTable SELECT 'This data is in testdb!'

go

sp_test

go

use master

go

sp_test

go

use master

go

drop database testdb

drop proc sp_test

drop table LocalTable

go

You expect the "LocalObject" result column to show the results of that table in the current context database, but it does not - it shows you the contents of that table in the master database!

I had a very hard time understanding (or finding any documentation / help on) this behaviour when I first started writing "sp_*" procs, but I haven't looked in a while; I guess it's generally discouraged anyway 🙂

If you really do want to change this behaviour (and see the correct result in the "LocalObject" column returned by the proc above), you need to use the "sp_ms_marksystemobject" stored procedure. Please note, it only makes sense to do this if you know that your proc will only ever be called from databases that will contain the local tables you are referencing.

In my case it makes sense for some management procs that need to work on dozens or hundreds of databases with essentially the same structure - it helps avoid using less-safe and harder-to-read "EXEC()" or "sp_executesql" calls.

(I was tempted to go write my own version of this QotD with the "trick", but felt it infringed a little on Anders' question - maybe something like that is coming anyway)

Hope this helps someone,

Tao