Blog Post

Core Infrastructure and Security Blog
1 MIN READ

Large table query, with rowcount

StevenRachui's avatar
StevenRachui
Icon for Microsoft rankMicrosoft
Jan 21, 2019

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

Updated Apr 07, 2020
Version 3.0
No CommentsBe the first to comment