sql语句在sql server中查询每个表的记录数，占用的空间，索引占用的空间等

本工具可以快速的实现数据表全库的查询

-- 查询数据库
declare   @id                 int                                                 
declare   @type                 character(2)                                   
declare                 @pages                 
int                                                 
declare   @dbname   sysname
declare   @dbsize   dec(15,0)
declare   @bytesperpage                 dec(15,0)
declare   @pagesperMB                                 dec(15,0)

create   table   #spt_space
(
  objid                                 int   null,
  rows                                 int   null,
  reserved                 dec(15)   null,
  data                                 dec(15)   null,
  indexp                                 dec(15)   null,
  unused                                 dec(15)   null
)
set   nocount   on

--   Create   a   cursor   to   loop   through   the   user       tables
declare   c_tables   cursor   for
select                 id
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   (objid,   reserved)
              select   objid   =   @id,   sum(reserved)  from   sysindexes  where   indid   in   (0,   1,   255)   and   id   =   @id
      select   @pages   =   sum(dpages) from   sysindexes where   indid   <   2 and   id   =   @id
      select   @pages   =   @pages   +   isnull(sum(used),   0)
              from   sysindexes where   indid   =   255 and   id   =   @id
      update   #spt_space set   data   =   @pages
      where   objid   =   @id

      /*   index:   sum(used)   where   indid   in   (0,   1,   255)   -   data   */
      update   #spt_space  set   indexp   =   (select   sum(used) from   sysindexes where   indid   in   (0,   1,   255)  and   id   =   @id)
                          -   data where   objid   =   @id

      /*   unused:   sum(reserved)   -   sum(used)   where   indid   in   (0,   1,   255)   */
      update   #spt_space set   unused   =   reserved   -   (select   sum(used)  from   sysindexes where   indid   in   (0,   1,   255)  and   id   =   @id)
              where   objid   =   @id

      update   #spt_space set   rows   =   i.rows  from   sysindexes   i where   i.indid   <   2  and   i.id   =   @id and   objid   =   @id
      fetch   next   from   c_tables
      into   @id
end

select                   TableName   =   (select   left(name,60)   from   sysobjects   where   id   =   objid),
      Rows   =   convert(char(11),   rows),
      ReservedKB   =   ltrim(str(reserved   *   d.low   /   1024.,15,0)   +   '   '   +   'KB'),
      DataKB   =   ltrim(str(data   *   d.low   /   1024.,15,0)   +   '   '   +   'KB'),
      IndexSizeKB   =   ltrim(str(indexp   *   d.low   /   1024.,15,0)   +   '   '   +   'KB'),
      UnusedKB   =   ltrim(str(unused   *   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   
drop   table   #spt_space
close   c_tables
deallocate   c_tables