2010-01-26

sp_spaceused可以查看某个表占用的空间，但不能一次查看所有的表。今天研究了一下这个sp，写了下面这个查询：

--刷新系统数据

dbcc updateusage(0) with no_infomsgs

--每页8k

select name = name,id,

rows = convert(char(11), rows),

reserved = ltrim(str(reservedpages * 8.,15,0) + ' KB'),

data = ltrim(str(pages * 8.,15,0) + ' KB'),

index_size = ltrim(str((usedpages - pages) * 8.,15,0) + ' KB'),

unused = ltrim(str((reservedpages - usedpages) * 8.,15,0) + ' KB')

FROM

(select name,id,

reservedpages = sum(a.total_pages),

usedpages = sum(a.used_pages),

pages = sum(

    CASE

     When a.type <> 1 Then a.used_pages

     When p.index_id < 2 Then a.data_pages

     Else 0

    END

   ),

rows = sum(

    CASE

     When (p.index_id < 2) and (a.type = 1) Then p.rows

     Else 0

    END

   )

from sys.partitions p, sys.allocation_units a,sysobjects o

where p.partition_id = a.container_id and p.object_id = o.id

group by name,id

) a

order by reservedpages desc