sqlserver2012查看表大小情况

执行一下代码：

--定义表变量
--定义表变量
DECLARE @T TABLE
(
	[Name]             VARCHAR(max),
	[Rows]             INT,
	[Reserved]         VARCHAR(max),
	[Data_size]        VARCHAR(max),
	[Index_size]       VARCHAR(max),
	[Unused_size]      VARCHAR(max)
)
 
--将表占用情况存放到表变量
INSERT INTO @T
EXEC sp_MSforeachtable "exec sp_spaceused '?'"
 
SELECT	[Name] AS [表名],
		[Rows] AS [行数],
		[Reserved] AS [全部大小_kb],
		[Data_size] AS [数据大小_kb],
		[Index_size] AS [索引大小_kb],
		[Unused_size] AS [剩余大小_kb],
	CAST(REPLACE([Reserved],'KB','') AS INT)/1024 AS [全部大小_mb],
	CAST(REPLACE([Data_size],'KB','') AS INT)/1024 AS [数据大小_mb],
	CAST(REPLACE([Index_size],'KB','') AS INT)/1024 AS [索引大小_mb],
	CAST(REPLACE([Unused_size],'KB','') AS INT)/1024 AS [剩余大小_mb]
FROM @T
order by CAST(REPLACE(reserved,'KB','') AS INT) desc
 

执行效果如下：