List table sizes in SQL

I needed to get a list of table sizes from SQL to find out why a database was excessively large.  I used the following:

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, CONVERT(bigint, left(reserved, len(reserved)-3)) AS SizeinKB

FROM #RowCountsAndSizes

ORDER BY NumberOfRows DESC, SizeinKB DESC, TableName

DROP TABLE #RowCountsAndSizes

Leave a Reply

Your email address will not be published. Required fields are marked *