在sql server 2008中,有没有一种方法可以生成关于每个表占用多少磁盘空间的报告?

是的,使用 sp_spaceused 获取此信息的存储过程…

USE AdventureWorks;
GO
EXEC sp_spaceused N'Purchasing.Vendor';
GO

结果如下:

name    rows    reserved    data    index_size  unused
Vendor  104         48 KB   16 KB   32 KB   0 KB

要获取数据库中所有表的统计信息,请使用:

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

除了 @kzen's correct answer ,以获取单个结果集 那是可排序的 使用此:

declare @t table (name sysname,rows int,reserved nvarchar(40),data nvarchar(50),index_size nvarchar(50),unused nvarchar(50))
INSERT INTO @T
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

--order by reserved space
select * from @t
order by CONVERT(int,LEFT(reserved,CHARINDEX(' ',reserved))) DESC

我同意建议的解决方案,但添加了总计查询。最后得到的查询是:

    Use [DatabaseName]        

    declare @t table 
    (name sysname,rows int,reserved nvarchar(40),data nvarchar(50),index_size nvarchar(50),unused nvarchar(50)) 

    INSERT INTO @T 
        EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

    --order by reserved space 
     select * from @t Order By [Name] -- order by CONVERT(int,LEFT(reserved,CHARINDEX(' ',reserved))) DESC 

    SELECT 
         Cast(Sum(Cast(Replace(reserved,' KB','') as int)) as varchar(12)) + ' KB' as Total_Reserved,
         Cast(Sum(Cast(Replace(data,' KB','') as int)) as varchar(12)) + ' KB' as Total_Data,
         Cast(Sum(Cast(Replace(index_size,' KB','') as int)) as varchar(12)) + ' KB' as Total_Index_Size,
         Cast(Sum(Cast(Replace(unused,' KB','') as int)) as varchar(12)) + ' KB' as Total_Unused
    FROM @T