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

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

创建存储过程：

CREATE PROCEDURE [dbo].[sys_viewTableSpace]

AS

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]

GO

2、建立统计存储过程：

create procedure HYspaceused

@objname nvarchar(776) = null,

@updateusage varchar(5) = false

as

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

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

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)

GO

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

GO

如何用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