SQL Tips #17 - Table Row Counts and Sizes in Gb

In SQL Tips #1 I showed how to list all tables with their row counts and sizes in Kb - here's the same query returning sizes in Gb instead.

The Query

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), unused VARCHAR(18))

EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,
ROUND(CONVERT(float,left(reserved,len(reserved)-3))/(1024*1024),2) AS SizeinGb
FROM #RowCountsAndSizes
ORDER BY NumberOfRows DESC,SizeinGb DESC,TableName

DROP TABLE #RowCountsAndSizes

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags