Monthly Archives: September 2010

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.

How to re-order report parameters

I added a parameter to a report today, which put it the end of the list the user fills in before running the report. I wanted to move it higher up the list but they are presented in a tree structure. You can edit each parameter but there’s no sign of dialogue that lists them (where you might be able to rearrange them). I looked in the help but couldn’t find anything. After a bit of trial and error it turns out you just highlight the parameter and use Ctrl and the up and down arrow keys. Easy when you know how!