SQLServer sp_MSforeachtable和sp_MSforeachdb用法

sp_MSforeachtable 和 sp_MSforeachdb 都是SQLserver 未正式公布（Undocumented）的存储过程，对管理员来说，经常需要在多库多表执行sql命令或者统计数据库信息都是比较方便的。

当前将用2个示例说明这2个存储过程是怎么使用的：

存储过程参数说明：

exec @return_value=sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand
exec @return_value=sp_MSforeachdb    @command1, @replacechar, @command2, @command3, @precommand, @postcommand

@return_value int					--返回值:0成功;其他失败
@command1 nvarchar(2000)			--第一个执行的命令
@replacechar nchar(1) = N'?'		--自定义表名称的符号,默认'?'
@command2 nvarchar(2000) = null		--第二个执行的命令
@command3 nvarchar(2000) = null		--第三个执行的命令
@whereand nvarchar(2000) = null		--过滤表名称的条件（sp_MSforeachdb 无此参数）
@precommand nvarchar(2000) = null	--执行存储过程前的命令
@postcommand nvarchar(2000) = null	--执行存储过程后的命令

sp_MSforeachtable 统计数据库中各表的空间分配情况：

--	drop  table #TabSpaceused
CREATE TABLE #TabSpaceused
(
	name sysname,
	rows int,	
	reserved sysname,	
	data sysname,
	index_size sysname,
	unused sysname,
)
GO

EXEC sp_MSforeachtable 
 @replacechar='?'
,@command1 = "insert into #TabSpaceused exec sp_spaceused '?'"
,@command2 = "update statistics ?"
,@command3 = "print '?'"
,@whereand = "and o.name not like 'conflict%' and o.name not like 'sys%' "
,@precommand="print 'Begin Time: '+convert(varchar(30),getdate(),121)"
,@postcommand="print 'End Time: '+convert(varchar(30),getdate(),121)"
GO

SELECT * FROM #TabSpaceused


;WITH TabSpaceused AS(
	SELECT name,rows
	,CONVERT(INT,LEFT(reserved,CHARINDEX(' ',reserved))) AS reserved
	,CONVERT(INT,LEFT(data,CHARINDEX(' ',data))) AS data
	,CONVERT(INT,LEFT(index_size,CHARINDEX(' ',index_size))) AS index_size
	,CONVERT(INT,LEFT(unused,CHARINDEX(' ',unused))) AS unused
	FROM #TabSpaceused
)
SELECT * FROM TabSpaceused

sp_MSforeachdb 统计数据库的空间分配情况：

--	select * from [master].[sys].[master_files]


--	drop  table #DBSpaceused
CREATE TABLE #DBSpaceused
(
	datebase sysname,
	fileid smallint,
	groupid smallint,
	size bigint,
	maxsize bigint,
	growth float,
	status int,
	perf int,
	name sysname,
	filename sysname
)
GO

EXEC sp_MSforeachdb 
 @replacechar='?'
,@command1 = "if '?' not in('master', 'model', 'msdb', 'tempdb') begin insert into #DBSpaceused select '?',* from ?.[dbo].[sysfiles] end"
,@command2 = "select count(0) as ?的表数量 from ?.sys.sysobjects where xtype='U'"
,@command3 = "print '?'"
,@precommand="print 'Begin Time: '+convert(varchar(30),getdate(),121)"
,@postcommand="print 'End Time: '+convert(varchar(30),getdate(),121)"
GO


select * from #DBSpaceused