Listing database table sizes in SQL Server

Today I wanted to put a copy of a database onto my laptop so I could access it offline – when travelling or away from home without access to the Internet. Unfortunately the database is pretty big and I didn’t really want to lose a lot of space just for that database. However I really only needed enough of it for development purposes, so I thought I’d delete out as much data as I could starting with the largest tables.

Looking at SQL Server there isn’t an obvious way to see the size of all tables in a convenient form. However, Microsoft do provide a stored procedure ‘sp_spaceused’ which gives information about the size of a single table. There is also a stored procedure ‘sp_MSforeachtable’ which will run some SQL on each table in the database. Combine these together and you can produce something that puts the table usage data into a temporary table:

EXECUTE sp_MSforeachtable
@precommand = 'DROP TABLE ##TableSizes; CREATE TABLE ##TableSizes
( name NVARCHAR(128),
rows CHAR(11),
reserved VARCHAR(50),
data VARCHAR(50),
index_size VARCHAR(50),
unused VARCHAR(50)
)',
@command1 = 'INSERT INTO ##TableSizes EXECUTE sp_spaceused [?];';
GO

Unfortunately the result isn’t as useful as it could be – the size values contain ‘KB’ on the end and the columns are set as VARCHAR so they won’t sort numerically. Fixing this is fortunately quite easy:

UPDATE ##TableSizes
SET reserved = SUBSTRING(reserved, 1, LEN(reserved)-3),
    data = SUBSTRING(data, 1, LEN(data)-3),
    index_size = SUBSTRING(index_size, 1, LEN(index_size)-3),
    unused = SUBSTRING(unused, 1, LEN(unused)-3);
ALTER TABLE ##TableSizes ALTER COLUMN reserved INT;
ALTER TABLE ##TableSizes ALTER COLUMN data INT;
ALTER TABLE ##TableSizes ALTER COLUMN index_size INT;
ALTER TABLE ##TableSizes ALTER COLUMN unused INT;

You now have a temporary table containing all the table size information and can easily query / sort / filter the information as you require, eg:

SELECT TOP 10 * FROM ##TableSizes ORDER BY data DESC;

– will show the top tables in terms of data stored.