SQL Server: show size of tables

| category: Programming | author: st
Tags:

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