查询 MSSQL，Sql Server 中查所有表数据大小

最新推荐文章于 2024-08-18 03:24:12 发布

ac.char

最新推荐文章于 2024-08-18 03:24:12 发布

阅读量592

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/sunny_day_day/article/details/111648349

版权

数据库专栏收录该内容

118 篇文章 2 订阅

订阅专栏

查询 MSSQL，Sql Server 中查所有表数据大小

-- 查询 MSSQL，Sql Server 中查所有表数据大小

declare @tablespaceinfo table ( nameinfo varchar(500),
rowsinfo int,
reserved varchar(200),
datainfo varchar(200),
index_size varchar(200),
unused varchar(200) ) declare @tablename varchar(255);

declare info_cursor cursor for
select
	[name]
from
	sys.tables
where
	type = 'u';

open info_cursor fetch next
from
info_cursor
into
	@tablename while @@fetch_status = 0 begin
insert
	into
	@tablespaceinfo exec sp_spaceused @tablename fetch next
from
	info_cursor
into
	@tablename
end close info_cursor deallocate info_cursor
select
	*,
	convert(varchar,
	(( cast(replace(reserved, 'kb', '') as int) + cast(replace(datainfo, 'kb', '') as int) + cast(replace(index_size, 'kb', '') as int) + cast(replace(unused, 'kb', '') as int) ) / (1024 * 1024) )) + ' g' as totaldata
from
	@tablespaceinfo
order by
	cast(replace(reserved, 'kb', '') as int) desc