First published on MSDN on May 24, 2009
Kevin has a number of very helpful queries on his blog – posted here . One of the most commonly used is the large table query. The large table query returns the space consumed by the table in kb, the space consumed by the index in kb and the space consumed by the blob in kb. One value I like to see as well is the row count for each table returned. I modified Kevins query to add this value and am posting it here for those that might also like this information in the same return set.
SELECT so.name, si.rowcnt as row_count,
8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb, Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb,
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id)
WHERE 'U' = so.type GROUP BY so.name, si.rowcnt ORDER BY data_kb DESC
