This is one of those posts where I just blurt out neat stuff I just learned! Today I talked to a man who knew a lot more about databases than I do (especially SQL Server) and one of the neat things he taught me were the SQL Server stored procedures sp_msForEachTable and sp_spaceused. This enabled me to put together a very wonderful little function to loop over all tables (using sp_msForEachTable) and fetch information about how much space they use (with sp_spaceused).
I created a function that puts it all in a temporary table because otherwise you just got a whole bunch of individual outputs which may be both hard to read and difficult to export:
CREATE TABLE #tableInformation ( name VARCHAR(255), rows INT, reserved varchar(255), data varchar(255), index_size varchar(255), unused varchar(255) ) INSERT #tableInformation EXEC sp_msForEachTable 'exec sp_spaceused ''?''' SELECT name, rows, reserved, data, index_size, unused FROM #tableInformation ORDER BY name DROP TABLE #tableInformation
The output can look like this: