Query to fetch table sizes and row counts quickly

Here’s a query that comes in handy when checking the size of your tables within a SQL Server Database:

SELECT st.name AS TableName,
	(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id) AS RowLength,
	si.rowcnt AS [RowCount],
	CAST(si.rowcnt*(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id)/1024.0/1024.0 AS INT) AS MaxTableSizeMB
FROM sys.tables as st
LEFT JOIN sysindexes si on st.object_id=si.id and si.indid < 2
ORDER BY st.name

Leave a Reply