简介

sp_msforeachdb 和 sp_msforeachtable 是非常有用的存储过程, 他们允许你遍历SQL Server实例中的全部数据库和表, 并对这些数据库和表执行命令. 我作为一个DBA每天都在使用它们. 两个存储过程都使用问号(?)作为一个置换符. 对于sp_msforeachdb, “?”表示数据库名称, 对于sp_msforeachtable, “?”表示表名称.
sp_msforeachdb

示例 #1 – 可以使用以下命令检查实例中的每个数据库:

sp_msforeachdb ‘dbcc checkdb( ”?” )’

示例 #2 – 修改实例中所有数据库的所有者为sa.

sp_msforeachdb ‘IF ”?” NOT IN (”master”, ”model”, ”msdb”, ”tempdb”)
BEGIN
print ”?”
exec [?].dbo.sp_changedbowner ”sa”
END’

**注意: 我使用if语句来过滤掉系统数据库

示例 #3 – 可以使用以下命令检查数据库中的每个表:

sp_msforeachdb ‘dbcc checktable( ”?” )’

示例 #4 – 压缩实例中的所有数据库. 谨慎使用这个命令. 不要在业务时间使用在业务数据库上.

sp_msforeachdb ‘dbcc ShrinkDatabase( ?, 10 )’

示例 #5 – 为实例中所有数据库添加用户 db_owner. 通常使用在SharePoint安装补丁上.

sp_msforeachdb ‘IF ”?” NOT IN (”master”, ”model”, ”msdb”, ”tempdb”)
BEGIN
print ”?”

exec [?].dbo.sp_adduser ”<YOUR DOMAIN NAME HERE>\<YOUR USER ACCOUNT HERE>”
exec [?].dbo.sp_addrolemember ”db_owner”,”<YOUR DOMAIN NAME HERE>\
<YOUR USER ACCOUNT HERE”
END’

sp_msforeachtable

相当于sp_msforeachdb, 存储过程sp_msforeachtable在命令中使用”?”来代替表名称.

示例 #1 – 获取索引列表, 以及索引的统计数据.

CREATE table #stats(
table_name nvarchar(255) null,
index_name nvarchar(255) null,
statistics_update_date datetime null
)
GO

exec sp_msforeachtable
‘insert into #stats
SELECT
”?”,
name AS index_name,
STATS_DATE(object_id, index_id) AS statistics_update_date
FROM
sys.indexes
WHERE
object_id = OBJECT_ID(”?”);’

select * from #stats where index_name is not null

drop table #stats

有上百万人在使用这些存储过程, 可能不止这些. 你甚至可以组合使用sp_msforeachtable和sp_msforeachdb. 享受它们, 同时小心使用.

原文: SQL Server Hidden Stored Procedures

作者: Michael Ceranski

PS: 很久没有做过翻译了, 但是这篇实在是很值得推荐, 顺便复习一下自己的英语. 希望没有给大家带来混乱:-)