I came across a bit of an eye-opener the other day in the SQL Server 2008 Internals book. You know how you can create your own stored proc in master with a name that starts with ‘sp_’ and then you can call it from any other database? Well if you create a table in master with a name that starts with ‘sp_’ you can read and write to it from any database too. Try this out:
use master; go create table dbo.sp_temp ( tempid int not null , tempdata varchar(10) not null ); go use <someuserdatabase>; go insert dbo.sp_temp (tempid, tempdata) values (1, 'One'); select * from dbo.sp_temp; go use master; go drop table dbo.sp_temp; go
It doesn’t work if the table name doesn’t start with ‘sp_’. The example in the book (page 190) was about capturing the output from DBCC LOGINFO for all databases into a table for further analysis. This tool probably shouldn’t become your new hammer for use on every screw in sight but it’s very handy in certain circumstances.