奇怪！但的确是真的：关于以sp_开头的表

Strange but True: sp_ tables

 

http://sqlblogcasts.com/blogs/davidwimbush/archive/2010/09/22/strange-but-true-sp-tables.aspx

奇怪！但的确是真的：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:

在我读SQL Server 2008 Internals book（SQL Server2008内幕）的第二天时碰到了一个让我开眼界的东东。大家知道我们可以在master库中创建以‘sp_’开头的自定义存储过程，该存储过程可以在别的库中调用。你是否也可以在master库中创建以‘sp_’开头的表，该表可以在别的库中进行读写呢？试试看：

（译者注：该语句同样可以在SQL Server2005中运行。）

 

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.

如果表名不以‘sp_’开头，上述语句将不能工作。该书中190页的例子就是在所有数据库中获取DBCC LOGINFO的输出结果到一个表中以供进一步分析。你或许不能随意使用这个工具（特性）但是在某些特定的场合中该工具不失为你的一个“法宝”。