[SQL Server]数据库大小,表大小及行数统计

末蓝、 2022-09-27 14:26 172阅读 0赞

\-- 统计某个表的空间大小, 行数信息

EXECYour\_DB.dbo.sp\_spaceused'dbo.t\_log';

\-- 统计多个数据库多个表的空间大小, 行数信息

createtabletmp\_table\_space(table\_namevarchar(50),table\_rowsint,total\_sizevarchar(20),data\_sizevarchar(20),index\_sizevarchar(20),

unused\_sizevarchar(20),dbnamevarchar(30));

insertintotmp\_table\_space(table\_name,table\_rows,total\_size,data\_size,index\_size,unused\_size)

EXECYour\_DB.dbo.sp\_spaceused'dbo.t\_log';

insertintotmp\_table\_space(table\_name,table\_rows,total\_size,data\_size,index\_size,unused\_size)

EXECYour\_DB.dbo.sp\_spaceused'dbo.t\_log2';

updatetmp\_table\_spacesetdbname='AHBZMJ'wheredbnameisnull;

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

用于查看对应数据库的大小、占用空间以及该数据库中各个系统表、用户表

使用方法：  在查询分析器中选择您要查看的数据库，然后运行此代码即可。

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

\----新建一个表spt\_result\_table存储数据库中各个表的空间信息

ifexists(select \* from dbo.sysobjects where id = object\_id(N'\[dbo\].\[spt\_result\_table\]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

droptable \[dbo\].\[spt\_result\_table\]

createtable spt\_result\_table

(

tablename       varchar(776)      null,  \----表名

rows varchar(776)      null,  \----表中现有的行数

reserved varchar(776)      null,  \----为表保留的空间总量

data varchar(776)      null,  \----表中的数据所使用的空间量

indexp varchar(776)      null,  \----表中的索引所使用的空间量

unused varchar(776)      null   \----表中未用的空间量

)ON\[PRIMARY\]

\----创建存储过程prc\_database\_spaceused：计算数据库大小及各个表占用空间的情况

ifexists(select \* from dbo.sysobjects where id = object\_id(N'\[dbo\].\[prc\_database\_spaceused\]')

and OBJECTPROPERTY(id, N'IsProcedure') = 1)

dropprocedure \[dbo\].\[prc\_database\_spaceused\]

createprocedure prc\_database\_spaceused

BEGIN

declare @id                 int

declare @type         character(2)

declare @pages         int

declare @dbname             sysname           \----数据库名

declare @dbsize             dec(15,0)         \----数据库大小

declare @logsize            dec(15)

declare @bytesperpage dec(15,0)

declare @pagesperMB dec(15,0)

declare @objname            varchar(776)       \----记录表名

declare @database\_size       varchar(776)

declare @unallocated\_space   varchar(776)

select  @dbname = db\_name()                   \----数据库为当前数据库

create table \#spt\_space

(

rows int     null,

reserved dec(15) null,         \----保留的空间总量

data dec(15) null,         \----数据使用的空间总量

indexp dec(15) null,         \----索引使用的空间

unused dec(15) null          \----未用的空间量

)

\---- 计算数据大小(以kB页为单位)

select  @dbsize = sum(convert(dec(15),size))

from    dbo.sysfiles

where   (status & 64 = 0)

\---- 计算日志大小(以kB页为单位)

select  @logsize = sum(convert(dec(15),size))

from    dbo.sysfiles

where   (status & 64 <> 0)

\---- 求得一个page有多少bytes

select  @bytesperpage = low

from    master.dbo.spt\_values

where   number = 1 and type = 'E'

\---- 计算MB占多少page（MB = 1048576B）

select  @pagesperMB = 1048576 / @bytesperpage

\---- 计算数据库大小

set  @database\_size   = ltrim(str((@dbsize \+ @logsize) / @pagesperMB,15,2) \+ ' MB')

\---- 计算未用的空间量的大小

set     @unallocated\_space   = ltrim(str((@dbsize \-

(selectsum(convert(dec(15),reserved))

from sysindexes

where indid in(0, 1, 255)

)) / @pagesperMB,15,2)+ ' MB')

\---- 保留的空间总量

insert into \#spt\_space(reserved)

select sum(convert(dec(15),reserved))

from   sysindexes

where  indid in(0, 1, 255)

select @pages = sum(convert(dec(15),dpages))

from   sysindexes

where  indid < 2

select @pages = @pages \+ isnull(sum(convert(dec(15),used)), 0)

from   sysindexes

where  indid = 255

\---- 数据使用的空间总量

update \#spt\_space

set    data = @pages

\---- 索引使用的空间

update \#spt\_space

set    indexp =(select sum(convert(dec(15),used))

from   sysindexes

where  indid in(0, 1, 255))- data

\---- 未用的空间量

update \#spt\_space

set    unused = reserved \-(select sum(convert(dec(15),used))

from   sysindexes

where  indid in(0, 1, 255))

\---- 输出数据库大小信息

select

database\_name     = @dbname,

database\_size     = @database\_size,

unallocated\_space = @unallocated\_space,

reserved          = ltrim(str(reserved \* d.low / 1024.,15,0) \+ ' ' \+ 'KB'),

data              = ltrim(str(data \* d.low / 1024.,15,0) \+ ' ' \+ 'KB'),

index\_size        = ltrim(str(indexp \* d.low / 1024.,15,0) \+ ' ' \+ 'KB'),

unused            = 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'

\---- 清空临时表\#spt\_space

delete from  \#spt\_space

\-----定义游标，计算表大小信息

declare  cur\_table  cursor for

select   name

from     sysobjects

where    xtype = 'U' or xtype = 'S'

order    by  xtype  asc

\-----打开游标

open     cur\_table

fetch    next from cur\_table   into  @objname

while    (@@fetch\_status = 0)

begin

select @id = null

select @id = id,

@type = xtype

from   sysobjects

where  id = object\_id(@objname)

\--dbcc updateusage(0,@objname) with no\_infomsgs

insert into \#spt\_space(reserved)

select 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

update \#spt\_space

set    indexp =(select sum(used)

from   sysindexes

where  indid in(0, 1, 255)

and    id = @id) \- data

update \#spt\_space

set    unused = reserved \-(select sum(used)

from   sysindexes

where  indid in(0, 1, 255)

and    id = @id)

update \#spt\_space

set    rows = i.rows

from   sysindexes i

where  i.indid < 2

and    i.id = @id

insert into spt\_result\_table

select tablename = object\_name(@id),

rows = convert(char(11), rows),

reserved = ltrim(str(reserved \* d.low / 1024.,15,0) \+  ' ' \+ 'KB'),

data = ltrim(str(data \* d.low / 1024.,15,0) \+  ' ' \+ 'KB'),

index\_size = ltrim(str(indexp \* d.low / 1024.,15,0) \+ ' ' \+ 'KB'),

unused = 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'

truncate table  \#spt\_space

fetch   next from cur\_table   into @objname

end

close        cur\_table

deallocate   cur\_table

select \* from spt\_result\_table where tablename is not null;

drop table \#spt\_space

END

execprc\_database\_spaceused

\---- 删除spt\_result\_table表

ifexists(select \* from dbo.sysobjects where id = object\_id(N'\[dbo\].\[spt\_result\_table\]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

droptable \[dbo\].\[spt\_result\_table\]

\---- 删除存储过程prc\_database\_spaceused

ifexists(select \* from dbo.sysobjects where id = object\_id(N'\[dbo\].\[prc\_database\_spaceused\]')

and OBJECTPROPERTY(id, N'IsProcedure') = 1)

dropprocedure \[dbo\].\[prc\_database\_spaceused\]

[SQL Server]数据库大小,表大小及行数统计

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关表元数据统计 | 统计MySQL数据表大小

相关统计HDFS目录下文件行数及文件大小

相关 SQL Server查询表行数

相关 PostgreSQL 查询所有数据库大小，表大小，索引大小，以及表空间大小

相关统计mysql说有表的大小_统计mysql数据库、表大小_MySQL

相关 [SQL Server]数据库大小,表大小及行数统计

相关 Mysql统计库表大小sql整理

相关 SQL Server查看所有表大小，所占空间

相关 HBASE 查看表记录数及表大小

相关查询mysql数据库中表的行数与大小