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的输出结果到一个表中以供进一步分析。你或许不能随意使用这个工具(特性)但是在某些特定的场合中该工具不失为你的一个“法宝”。