如何获取SQL Server数据库里表的占用容量大小
其实只要使用系统内置的存储过程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'.') and
OBJECTPROPERTY(id, N'IsUserTable') =
1)
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifcreate
tabletablespaceinfo --创建结果存储表
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.gifif
exists (select
*
from dbo.sysobjects where id =
object_id(@tablename) and
OBJECTPROPERTY(id, N'IsUserTable') =
1)
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifexecute sp_executesql
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gif N'insert into tablespaceinfoexec 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.gifFETCH
NEXT
FROM Info_cursor
http://keenx.cnblogs.com/Images/OutliningIndicators/None.gifINTO
@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
页:
[1]