如何获取SQL Server数据库里表的占用容量大小

ehxz

其实只要使用系统内置的存储过程sp_spaceused就可以得到表的相关信息
如：sp_spaceused 'tablename'

以下是为了方便写的一个存储过程，目的是把当前的所有表的相关信息全部都保存在一个指定的表里面
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifCREATE
PROCEDURE get_tableinfo AS
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifif
not
exists (select
*
from dbo.sysobjects where id =
object_id(N'[dbo].[tablespaceinfo]') and
OBJECTPROPERTY(id, N'IsUserTable') =
1)
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifcreate
table  tablespaceinfo                         --创建结果存储表
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif              (nameinfo varchar(50) ,  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif               rowsinfo int , reserved varchar(20) ,  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif               datainfo varchar(20)  ,  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif               index_size varchar(20) ,  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif               unused varchar(20) )
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifdelete
from tablespaceinfo --清空数据表
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifdeclare
@tablename
varchar(255)  --表名称
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifdeclare
@cmdsql
varchar(500)
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifDECLARE Info_cursor CURSOR
FOR  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifselect o.name   
http://keenx.cnblogs.com/Images/OutliningIndicators/None.giffrom dbo.sysobjects o where
OBJECTPROPERTY(o.id, N'IsTable') =
1  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif     and o.name not
like N'#%%'
order
by o.name
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifOPEN Info_cursor
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifFETCH
NEXT
FROM Info_cursor  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifINTO
@tablename  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifWHILE
@@FETCH_STATUS
=
0
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifBEGIN
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif  if
exists (select
*
from dbo.sysobjects where id =
object_id(@tablename) and
OBJECTPROPERTY(id, N'IsUserTable') =
1)
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif  execute sp_executesql  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif         N'insert into tablespaceinfo  exec sp_spaceused @tbname',
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif          N'@tbname varchar(255)',
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif          @tbname
=
@tablename
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif  FETCH
NEXT
FROM Info_cursor  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif  INTO
@tablename  
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifEND
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifCLOSE Info_cursor
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifDEALLOCATE Info_cursor
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifGO
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif

执行存储过程
exec get_tableinfo

查询运行该存储过程后得到的结果
select *
from tablespaceinfo  
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc