sql server中查询每个表的记录数代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 sql server中查询每个表的记录数

作者:Dezai.CN / 发布于2012/6/7/ 656

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

<div>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</div>

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)