遍历所有数据库，但不包括系统数据库。列出表所在数库名，表名，表的大小，表的记录数。非常有趣，T-SQL 看下面：
1: create table #t(d nvarchar(MAX),t nvarchar(MAX),r int,x nvarchar(100),s nvarchar(100),y nvarchar(100),z nvarchar(100))
2: declare @s nvarchar(MAX)
3: set @s=replace('if !~! not in (!master!,!model!,!msdb!,!tempdb!) exec [~].dbo.sp_msforeachtable "insert into #t(t, r,x,s,y,z) exec [~].dbo.sp_spaceused !?!"','!',char(39))
4: EXEC sp_MSForEachDB @command1=@s, @command2="update #t set d='~' where d is null", @replacechar='~'
5: select top(5) d as base, t as [table], s as size, r as rows from #t order by Cast(LEFT(s,len(s)-3) as int) desc
6: drop table #t
结果类似如下：
BASE TABLE SIZE ROWS
----------------------------------
base1 T1 162720 KB 14233
base2 T2 38000 KB 8462
base1 T3 10720 KB 5343
base1 T4 9040 KB 5006
base3 T5 2800 KB 1899
Reference:TSQL Challenge 7
希望对您有所帮助
Author: Petter Liu http://wintersun.cnblogs.com
