SQLServer各テーブルの容量

久々にSQL Server使ったのでメモ

DECLARE @table TABLE( 
		[TableName] SYSNAME, 
		[rows] BIGINT,
		[reserved] VARCHAR(25),
		[data] VARCHAR(25),
		[index_size] VARCHAR(25),
		[unused] VARCHAR(25)
		 )
INSERT INTO @table
EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'"

--SELECT * FROM @table
SELECT
  TableName,
  rows,
  CONVERT( bigint, REPLACE(reserved, 'KB', '')) / 1024 as reserved_mb,
  CONVERT( bigint, REPLACE(data, 'KB', '')) /1024 as data_mb,
  CONVERT( bigint, REPLACE(index_size, 'KB', '')) / 1024 as index_size_mb,
  CONVERT( bigint, REPLACE(unused, 'KB', '')) / 1024 as unused_mb
FROM
 @table
ORDER BY
 reserved_mb DESC

GO