关于查看MSSQL 数据库 用户每个表 占用的空间大小
程序员文章站
2023-11-06 19:17:28
最近做项目需要查看数据用户表的大小,包括记录条数和占用的磁盘空间数目。在网上找了很久其中查看mssql数据库每个表占用的空间大小 相对还可以。不过它的2、3中方法返回的数据...
最近做项目需要查看数据用户表的大小,包括记录条数和占用的磁盘空间数目。在网上找了很久其中查看mssql数据库每个表占用的空间大小 相对还可以。
不过它的2、3中方法返回的数据比较多,有些是我们不关心的数据,我在adventureworks2012数据上做的测试。其中第二种方法代码如下:
view code
if not exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[tablespaceinfo]') and objectproperty(id, n'isusertable') = 1)
create table tablespaceinfo --创建结果存储表
(nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20) )
delete from tablespaceinfo --清空数据表
declare @tablename varchar(255) --表名称
declare @cmdsql varchar(500)
declare info_cursor cursor for
select o.name
from dbo.sysobjects o where objectproperty(o.id, n'istable') = 1
and o.name not like n'#%%' order by o.name
open info_cursor
fetch next from info_cursor
into @tablename
while @@fetch_status = 0
begin
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and objectproperty(id, n'isusertable') = 1)
execute sp_executesql
n'insert into tablespaceinfo exec sp_spaceused @tbname',
n'@tbname varchar(255)',
@tbname = @tablename
fetch next from info_cursor
into @tablename
end
close info_cursor
deallocate info_cursor
go
--itlearner注:显示数据库信息
sp_spaceused @updateusage = 'true'
--itlearner注:显示表信息
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
运行效果如图:
很显然这个返回结果是错误的。但是它提供了一种思路,修改后的sql语句如下:
view code
if not exists ( select *
from sys.tables
where name = 'tablespaceinfo' )
begin
create table tablespaceinfo --创建结果存储表
(
table_name varchar(50) ,
rows_count int ,
reserved int ,
datainfo int ,
index_size int ,
unused int
)
end
delete from tablespaceinfo
--清空数据表
create table #temp --创建结果存储表
(
nameinfo varchar(50) ,
rowsinfo int ,
reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20)
)
declare @tablename varchar(255)
--表名称
declare @cmdsql nvarchar(500)
declare info_cursor cursor
for
select '[' + table_schema + '].[' + table_name + ']' as table_name
from [information_schema].[tables]
where table_type = 'base table'
and table_name <> 'tablespaceinfo'
open info_cursor
fetch next from info_cursor
into @tablename
while @@fetch_status = 0
begin
set @cmdsql = 'insert into #temp exec sp_spaceused ''' + @tablename
+ ''''
execute sp_executesql @cmdsql
fetch next from info_cursor
into @tablename
end
close info_cursor
deallocate info_cursor
go
--itlearner注:显示数据库信息
--sp_spaceused @updateusage = 'true'
--itlearner注:显示表信息
update #temp
set reserved = replace(reserved, 'kb', '') ,
datainfo = replace(datainfo, 'kb', '') ,
index_size = replace(index_size, 'kb', '') ,
unused = replace(unused, 'kb', '')
insert into dbo.tablespaceinfo
select nameinfo ,
cast(rowsinfo as int) ,
cast(reserved as int) ,
cast(datainfo as int) ,
cast(index_size as int) ,
cast(unused as int)
from #temp
drop table #temp
select table_name ,
rows_count ,
case when reserved > 1024
then cast(reserved / 1024 as varchar(10)) + 'mb'
else cast(reserved as varchar(10)) + 'kb'
end as data_and_index_reserved ,
case when datainfo > 1024
then cast(datainfo / 1024 as varchar(10)) + 'mb'
else cast(datainfo as varchar(10)) + 'kb'
end as used ,
case when index_size > 1024
then cast(index_size / 1024 as varchar(10)) + 'mb'
else cast(index_size as varchar(10)) + 'kb'
end as index_size ,
case when unused > 1024 then cast(unused / 1024 as varchar(10)) + 'mb'
else cast(unused as varchar(10)) + 'kb'
end as unused
from dbo.tablespaceinfo
order by reserved desc
运行结果如图:
同时他的第三种方法返回的数据太多,很多是我们不怎么关心的,原sql语句如下:
view code
运行结果如图:
这里面包含一些索引信息,其实我们只关心表占用磁盘信息,修改后的sql语句如下:
view code
select object_name(id) tablename ,
case when reserved * 8 > 1024 then rtrim(8 * reserved / 1024) + 'mb'
else rtrim(reserved * 8) + 'kb'
end datareserve ,
case when dpages * 8 > 1024 then rtrim(8 * dpages / 1024) + 'mb'
else rtrim(dpages * 8) + 'kb'
end used ,
case when 8 * ( reserved - dpages ) > 1024
then rtrim(8 * ( reserved - dpages ) / 1024) + 'mb'
else rtrim(8 * ( reserved - dpages )) + 'kb'
end unused ,
case when ( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ) > 1024
then rtrim(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 )
/ 1024) + 'mb'
else rtrim(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ))
+ 'kb'
end free ,
rows as rows_count
from sys.sysindexes
where indid = 1
and status = 2066 -- status='18'
order by reserved desc
运行结果如下:
有不对的地方欢迎大家拍砖!
不过它的2、3中方法返回的数据比较多,有些是我们不关心的数据,我在adventureworks2012数据上做的测试。其中第二种方法代码如下:
复制代码 代码如下:
view code
if not exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[tablespaceinfo]') and objectproperty(id, n'isusertable') = 1)
create table tablespaceinfo --创建结果存储表
(nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20) )
delete from tablespaceinfo --清空数据表
declare @tablename varchar(255) --表名称
declare @cmdsql varchar(500)
declare info_cursor cursor for
select o.name
from dbo.sysobjects o where objectproperty(o.id, n'istable') = 1
and o.name not like n'#%%' order by o.name
open info_cursor
fetch next from info_cursor
into @tablename
while @@fetch_status = 0
begin
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and objectproperty(id, n'isusertable') = 1)
execute sp_executesql
n'insert into tablespaceinfo exec sp_spaceused @tbname',
n'@tbname varchar(255)',
@tbname = @tablename
fetch next from info_cursor
into @tablename
end
close info_cursor
deallocate info_cursor
go
--itlearner注:显示数据库信息
sp_spaceused @updateusage = 'true'
--itlearner注:显示表信息
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
运行效果如图:
很显然这个返回结果是错误的。但是它提供了一种思路,修改后的sql语句如下:
复制代码 代码如下:
view code
if not exists ( select *
from sys.tables
where name = 'tablespaceinfo' )
begin
create table tablespaceinfo --创建结果存储表
(
table_name varchar(50) ,
rows_count int ,
reserved int ,
datainfo int ,
index_size int ,
unused int
)
end
delete from tablespaceinfo
--清空数据表
create table #temp --创建结果存储表
(
nameinfo varchar(50) ,
rowsinfo int ,
reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20)
)
declare @tablename varchar(255)
--表名称
declare @cmdsql nvarchar(500)
declare info_cursor cursor
for
select '[' + table_schema + '].[' + table_name + ']' as table_name
from [information_schema].[tables]
where table_type = 'base table'
and table_name <> 'tablespaceinfo'
open info_cursor
fetch next from info_cursor
into @tablename
while @@fetch_status = 0
begin
set @cmdsql = 'insert into #temp exec sp_spaceused ''' + @tablename
+ ''''
execute sp_executesql @cmdsql
fetch next from info_cursor
into @tablename
end
close info_cursor
deallocate info_cursor
go
--itlearner注:显示数据库信息
--sp_spaceused @updateusage = 'true'
--itlearner注:显示表信息
update #temp
set reserved = replace(reserved, 'kb', '') ,
datainfo = replace(datainfo, 'kb', '') ,
index_size = replace(index_size, 'kb', '') ,
unused = replace(unused, 'kb', '')
insert into dbo.tablespaceinfo
select nameinfo ,
cast(rowsinfo as int) ,
cast(reserved as int) ,
cast(datainfo as int) ,
cast(index_size as int) ,
cast(unused as int)
from #temp
drop table #temp
select table_name ,
rows_count ,
case when reserved > 1024
then cast(reserved / 1024 as varchar(10)) + 'mb'
else cast(reserved as varchar(10)) + 'kb'
end as data_and_index_reserved ,
case when datainfo > 1024
then cast(datainfo / 1024 as varchar(10)) + 'mb'
else cast(datainfo as varchar(10)) + 'kb'
end as used ,
case when index_size > 1024
then cast(index_size / 1024 as varchar(10)) + 'mb'
else cast(index_size as varchar(10)) + 'kb'
end as index_size ,
case when unused > 1024 then cast(unused / 1024 as varchar(10)) + 'mb'
else cast(unused as varchar(10)) + 'kb'
end as unused
from dbo.tablespaceinfo
order by reserved desc
运行结果如图:
同时他的第三种方法返回的数据太多,很多是我们不怎么关心的,原sql语句如下:
复制代码 代码如下:
view code
select object_name(id) tablename ,
* reserved / 1024 reserved ,
rtrim(8 * dpages / 1024) + 'mb' used ,
* ( reserved - dpages ) / 1024 unused ,
* dpages / 1024 - rows / 1024 * minlen / 1024 free ,
rows
from sysindexes
where indid = 1
order by reserved desc
运行结果如图:
这里面包含一些索引信息,其实我们只关心表占用磁盘信息,修改后的sql语句如下:
复制代码 代码如下:
view code
select object_name(id) tablename ,
case when reserved * 8 > 1024 then rtrim(8 * reserved / 1024) + 'mb'
else rtrim(reserved * 8) + 'kb'
end datareserve ,
case when dpages * 8 > 1024 then rtrim(8 * dpages / 1024) + 'mb'
else rtrim(dpages * 8) + 'kb'
end used ,
case when 8 * ( reserved - dpages ) > 1024
then rtrim(8 * ( reserved - dpages ) / 1024) + 'mb'
else rtrim(8 * ( reserved - dpages )) + 'kb'
end unused ,
case when ( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ) > 1024
then rtrim(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 )
/ 1024) + 'mb'
else rtrim(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ))
+ 'kb'
end free ,
rows as rows_count
from sys.sysindexes
where indid = 1
and status = 2066 -- status='18'
order by reserved desc
运行结果如下:
有不对的地方欢迎大家拍砖!
上一篇: 太田道灌有着怎样的背景?有着怎样的功绩