--定义表变量
--定义表变量
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
原文链接https://www.cnblogs.com/hejjtianing/p/16802670.html