SQL SERVER: Checking all tables row count and size

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:

The result table
The result table

Leave a Reply