This was initially published by Kevin, and Steve added row count. I just changed the column names and posted it here for easy access to customers I work with. Anytime I engage in a tuning effort or a case where there are database issues, I first ask customers to run all queries on the main page that have a red asterisk (*).
/*Table size and record count*/
SELECT so.name AS 'Table 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)'
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