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.