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 mastergoif object_id('sp_test','p') is not nulldrop procedure sp_testgocreate procedure sp_testasselect db_name() + '.' + table_schema + '.' + table_name from information_schema.tablesorder by table_schema, table_namegoexec sp_testgouse adventureworksgoexec sp_testgouse mastergodrop procedure sp_testgo
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.