SQL Server テーブル毎のディスクサイズ取得 2022-08-24

declare @DiskUpsageByTables as table (
	name		NVARCHAR(128),
	rows		CHAR(11),
	reserved	VARCHAR(18),
	data		VARCHAR(18),
	index_size	VARCHAR(18),
	unused		VARCHAR(18)
);

INSERT INTO @DiskUpsageByTables
EXEC sp_MSforeachtable 'sp_spaceused ''?''';

SELECT
	spaceused.name
	, spaceused.rows
	, (cast(spaceused.rows as bigint) / 10000) as rows_man
	, spaceused.reserved
	, (cast(replace(spaceused.reserved, ' KB', '') as bigint) / 1024 / 1024) as reserved_GB
	, spaceused.data
	, spaceused.index_size
	, spaceused.unused
FROM
	@DiskUpsageByTables as spaceused
ORDER BY
	CAST(REPLACE(reserved, ' KB', '') AS INT) DESC;

tags: SQL Server ディスク容量 sp_MSforeachtable