sqlserver查看所以表大小 表行数 表索引大小

本文章只做个人记录，转自：吉普赛的歌

代码：

--定义表变量
DECLARE @T TABLE
(
	[name]           VARCHAR(max),
	[rows]           INT,
	reserved         VARCHAR(max),
	data_size        VARCHAR(max),
	index_size       VARCHAR(max),
	unused           VARCHAR(max)
)
 
--将表占用情况存放到表变量
INSERT INTO @T
EXEC sp_MSforeachtable "exec sp_spaceused '?'"
 
SELECT [name],[rows],reserved,data_size,index_size,unused,
	CAST(REPLACE(reserved,'KB','') AS INT)/1024 as reserved_MB,
	CAST(REPLACE([data_size],'KB','') AS INT)/1024 as data_size_MB,
	CAST(REPLACE([index_size],'KB','') AS INT)/1024 as index_size_MB,
	CAST(REPLACE([unused],'KB','') AS INT)/1024 as unused_MB
FROM @T
order by CAST(REPLACE(reserved,'KB','') AS INT) desc

运行结果：