Add new comment

SQL Server: show size of tables

Submitted by Serguei_Tarassov on

How much of disk space is used by tables? 

DECLARE @TableInfo TABLE (
	table_name sysname,
	row_count int,
	reserved_size_kb nvarchar(50),
	data_size_kb nvarchar(50),
	index_size_kb nvarchar(50),
	unused_size_kb nvarchar(50)
)
 
INSERT INTO @TableInfo
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
 
UPDATE @TableInfo 
SET 
	data_size_kb     = replace(data_size_kb, 'KB', ''),
	reserved_size_kb = replace(reserved_size_kb, 'KB', ''),
	index_size_kb    = replace(index_size_kb, 'KB', ''),
	unused_size_kb   = replace(unused_size_kb, 'KB', '')
 
SELECT * FROM @TableInfo 
ORDER BY convert(int, data_size_kb) DESC