Strange but True: sp_ tables

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s