查询SQL数据库中各表大小的存储过程

一、进入数据库，选择master数据库，执行下列存储过程

create procedure sp_tablespace

as 
  create table #spt_space 
(
 id int null,
 type char(10) null,
 name sysname null,
 rows  int null, 
 reserved dec(15) null, 
 used dec(15) null,
 data  dec(15) null, 
 indexp  dec(15) null, 
 unused  dec(15) null 
) 

 create table #spt_space2 
(
 id int null,
 type char(10) null,
 name sysname null,
 rows  int null, 
 reserved dec(15) null,
 used dec(15) null, 
 data  dec(15) null, 
 indexp  dec(15) null, 
 unused  dec(15) null 
) 

 insert into #spt_space (id,type,name)
 select id,xtype,name
  from sysobjects 
 
 insert into #spt_space2 (id,type,name)
 select id,xtype,name
  from sysobjects 
  
set nocount on 
 
DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS

 insert into #spt_space2 (id,reserved)
  select B.id,sum(sysindexes.reserved) 
   from sysindexes,#spt_space B
    where indid in (0, 1, 255) 
     and sysindexes.id = B.id group by B.id
 
  insert into #spt_space2 (id,data)
   select B.id,sum(sysindexes.dpages) 
   from sysindexes,#spt_space B 
    where indid < 2 
     and sysindexes.id =  B.id  group by B.id
    
 insert into  #spt_space2 (id,data)
   select B.id,isnull(sum(sysindexes.used), 0) 
  from sysindexes,#spt_space B 
   where indid = 255 
    and sysindexes.id =  B.id  group by B.id
 
  insert into  #spt_space2 (id,used)
     select B.id,sum(sysindexes.used) 
    from sysindexes,#spt_space B 
     where indid in (0, 1, 255) 
      and sysindexes.id =  B.id  group by B.id        
 
 delete from #spt_space
 
 insert into #spt_space select id,max(type),max(name),sum(rows),
     sum(reserved),sum(used),sum(data),sum(indexp),sum(unused) from #spt_space2 group by id
 
 update #spt_space 
  set unused =reserved - used 
 
update #spt_space 
  set indexp =used - data  
        
 update #spt_space set rows = i.rows 
   from sysindexes i,#spt_space B
    where i.indid < 2 
     and i.id =  B.id 
 
 delete from #spt_space where isnull(reserved,0)=0
 
 update #spt_space set type='系统表' where type='S'
 
 update #spt_space set type='用户表' where type='U'
 
 select  #spt_space.id,'类型'=#spt_space.type,'名字'=#spt_space.name,
  '行数' = convert(char(11), rows), 
  '分配空间' = ltrim(str(reserved * d.low / 1024.,15,0) + 
    ' ' + 'KB'), 
  '已使用空间' = ltrim(str(used * d.low / 1024.,15,0) + 
    ' ' + 'KB'),
  '未使用空间' = ltrim(str(unused * d.low / 1024.,15,0) + 
    ' ' + 'KB'), 
  '数据使用' = ltrim(str(data * d.low / 1024.,15,0) + 
    ' ' + 'KB'), 
  '索引使用' = ltrim(str(indexp * d.low / 1024.,15,0) + 
    ' ' + 'KB')
 from #spt_space, master.dbo.spt_values d 
  where d.number = 1 
   and d.type = 'E' order by reserved DESC    
 
return (0) -- sp_tablespace

 

二、执行sp_tablespace后，即可得知表大小