SQL计算数据库表占用的真实空间大小

最近客户的数据库暴涨,一开始找不到原因,用sql查询表的空间大小,结果之前用的sql语句只能查询除了text,image外的字段大小.搞得一时间混乱不堪.后来咨询了大师们,找到用sp_spaceused这个系统函数可以查询表的真实大小,包含text等字段.sql如下:

?

declare @id            NVARCHAR(100) create table #spt_space (      [ name ]            NVARCHAR(50) null ,      [ rows ]          int null ,      [reserved]       NVARCHAR(50) null ,      [data]         NVARCHAR(50) null ,      [index_size]        NVARCHAR(50) null ,      [unused]         NVARCHAR(50) null )   set nocount on   declare c_tables cursor for select name from sysobjects where xtype = 'U'   open c_tables fetch next from c_tables into @id   while @@fetch_status = 0 begin      /* Code from sp_spaceused */      insert into #spt_space ([ name ],[ rows ],reserved,data,index_size,unused)      EXEC sp_spaceused @id      fetch next from c_tables   into @id   end SELECT * FROM ( SELECT *, CAST ( REPLACE (reserved, 'KB' , '' ) AS INT ) AS reservedindex FROM #spt_space ) c order by c.reservedindex desc   drop table #spt_space close c_tables deallocate c_tables

 

 

本文转自温景良(Jason)博客园博客，原文链接：http://www.cnblogs.com/wenjl520/archive/2011/07/01/2095320.html如需转载请自行联系原作者