本文章只做个人记录,转自:吉普赛的歌
代码:
--定义表变量
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
运行结果: