A while back, I wrote about using sp_ procedures in master to create a toolbox, which works fine for me... until recently I hit a strange problem. I developed a procedure that would read the definition of a table and would create a change_log table and trigger to populate that table. On my dev-environment (Windows Vista x64, SQL Server 2005 Dev Ed x64 SP2) this worked like I expected. But when I tested the procedure on a different server, it didn't!?! A very little sample that touches the essence of the problem I witnessed;
use master
go
if object_id('sp_test','p') is not null
drop procedure sp_test
go
create procedure sp_test
as
select db_name() + '.' + table_schema + '.' + table_name
from information_schema.tables
order by table_schema, table_name
go
exec sp_test
go
use adventureworks
go
exec sp_test
go
use master
go
drop procedure sp_test
go
Now on my dev machine, this once listed all tables in master and once all tables in AdventureWorks, as intended. But on other servers, it returned the tables from master on both executions of sp_test (the db_name function is executed correctly in both cases; once master, once AdventureWorks). For some reason, when referencing objects, the stored procedure stayed to master. I was puzzled, but before crying BUG (out loud) I tried the newsgroups and a prompt reply from SQL Server MVP Roy Harvey pointed me to the undocumented stored procedure sp_MS_marksystemobject. Simply execute;
exec sp_MS_marksystemobject sp_test
and you won't suffer from the inconsistent behavior I witnessed.