sqlserver表大小,sql查看表的数据大小

如何查看SQLServer数据库每个表占用的空间大小

创建存储过程：

成都创新互联专业为企业提供禹州网站建设、禹州做网站、禹州网站设计、禹州网站制作等企业网站建设、网页设计与制作、禹州企业网站模板建站服务，10年禹州做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

CREATE PROCEDURE [dbo].[sys_viewTableSpace]

BEGIN

SET NOCOUNT ON;

CREATE TABLE [dbo].#tableinfo(

表名 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

记录数 [int] NULL,

预留空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

使用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

索引占用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

未用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL

)

insert into #tableinfo(表名, 记录数, 预留空间, 使用空间, 索引占用空间, 未用空间)

exec sp_MSforeachtable "exec sp_spaceused '?'"

select * from #tableinfo

order by 记录数 desc

drop table #tableinfo

END

使用的时候直接：exec sys_viewtablespace

SQLServer表的大小与表占硬盘空间大小有什么区别，应该怎么去查这两项性能？

表的大小一般指表的行数，占用硬盘空间大小就比较复杂了，可以有以下几个参数：

分配空间，数据占用空间及索引占用空间。

执行

EXEC alltablecount

即可显示当前数据库所有表资源占用情况。

执行前，请前建立以下表，和两个存储过程：

1、先建立表：HY_SPACE

CREATE TABLE [HY_SPACE] (

[name] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,

[rows] [char] (11) COLLATE Chinese_PRC_CI_AS NULL ,

[reserved] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,

[data] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,

[index_size] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,

[unused] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

2、建立统计存储过程：

create procedure HYspaceused

@objname nvarchar(776) = null,

@updateusage varchar(5) = false

declare @idint

declare @typecharacter(2)

declare@pagesint

declare @dbname sysname

declare @dbsize dec(15,0)

declare @logsize dec(15)

declare @bytesperpagedec(15,0)

declare @pagesperMBdec(15,0)

create table #spt_space

(

rowsint null,

reserveddec(15) null,

datadec(15) null,

indexpdec(15) null,

unuseddec(15) null

)

if @updateusage is not null

begin

select @updateusage=lower(@updateusage)

if @updateusage not in ('true','false')

begin

raiserror(15143,-1,-1,@updateusage)

return(1)

end

if @objname IS NOT NULL

begin

select @dbname = parsename(@objname, 3)

if @dbname is not null and @dbname db_name()

begin

raiserror(15250,-1,-1)

return (1)

end

if @dbname is null

select @dbname = db_name()

select @id = null

select @id = id, @type = xtype

from sysobjects

where id = object_id(@objname)

if @id is null

begin

raiserror(15009,-1,-1,@objname,@dbname)

return (1)

end

if not exists (select * from sysindexes

where @id = id and indid 2)

if @type in ('P ','D ','R ','TR','C ','RF')

begin

raiserror(15234,-1,-1)

return (1)

end

else if @type = 'V '

begin

raiserror(15235,-1,-1)

return (1)

end

else if @type in ('PK','UQ')

begin

raiserror(15064,-1,-1)

return (1)

end

else if @type = 'F '

begin

raiserror(15275,-1,-1)

return (1)

end

if @updateusage = 'true'

begin

if @objname is null

dbcc updateusage(0) with no_infomsgs

else

dbcc updateusage(0,@objname) with no_infomsgs

print ' '

end

set nocount on

if @id is null

begin

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

from dbo.sysfiles

where (status 64 = 0)

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

from dbo.sysfiles

where (status 64 0)

select @bytesperpage = low

from master.dbo.spt_values

where number = 1

and type = 'E'

select @pagesperMB = 1048576 / @bytesperpage

select database_name = db_name(),

database_size =

ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),

'unallocated space' =

ltrim(str((@dbsize -

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

from sysindexes

where indid in (0, 1, 255)

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

print ' '

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

end

else

begin

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 HY_SPACE

select name = 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'

RETURN

end

return (0)

3、主存储过程

create procedure alltablecount as

declare @name varchar(100)

TRUNCATE TABLE HY_SPACE

declare tablecur cursor for select name from sysobjects where xtype= 'u '

create table #tablecount(tablename varchar(100),reccount int)

open tablecur

fetch next from tablecur into @name

while @@fetch_status!=-1

begin

exec ('insert into #tablecount select tablename='''+@name+''',reccount=count(1) from '+@name+' ')

EXEC HYspaceused @NAME

fetch next from tablecur into @name

end

close tablecur

deallocate tablecur

select * from #tablecount order by reccount desc

select * from HY_SPACE

如何用sql语句查看某个数据库中的表的大小

--读取库中的所有表名

select name from sysobjects where xtype='u'

--读取指定表的所有列名

select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名')

获取数据库表名和字段

sqlserver中各个系统表的作用

sysaltfiles 主数据库保存数据库的文件

syscharsets 主数据库字符集与排序顺序

sysconfigures 主数据库配置选项

syscurconfigs 主数据库当前配置选项

sysdatabases 主数据库服务器中的数据库

syslanguages 主数据库语言

syslogins 主数据库登陆帐号信息

sysoledbusers 主数据库链接服务器登陆信息

sysprocesses 主数据库进程

sysremotelogins主数据库远程登录帐号

syscolumns 每个数据库列

sysconstrains 每个数据库限制

sysfilegroups 每个数据库文件组

sysfiles 每个数据库文件

sysforeignkeys 每个数据库外部关键字

sysindexs 每个数据库索引

sysmenbers 每个数据库角色成员

sysobjects 每个数据库所有数据库对象

syspermissions 每个数据库权限

systypes 每个数据库用户定义数据类型

select 列名=name from syscolumns where id=object_id(N'要查的表名')

sqlserver怎么看表的大小

select tabname as '表名',rowsNum as '表数据行数',reserved as '保留大小',data as '数据大小',index_size as '索引大小',unused_size as '未使用大小'

from #tabName

--where tabName not like 't%'

order by cast(rowsNum as int) desc

本文名称：sqlserver表大小,sql查看表的数据大小
分享URL：http://azwzsj.com/article/dsspopp.html

sqlserver表大小,sql查看表的数据大小

如何查看SQLServer数据库每个表占用的空间大小

SQLServer表的大小与表占硬盘空间大小有什么区别，应该怎么去查这两项性能？

如何用sql语句查看某个数据库中的表的大小

sqlserver怎么看表的大小

其他资讯