Table Size Query

The following query lists the tables and the space they use. This query is much faster (sub-second) than a standard SELECT COUNT(*) query since it uses the dynamic management views in SQL Server rather than scanning your data.

SELECT sum ( used_page_count ) * 8 AS SizeKB,   sum(row_count) AS [RowCount], object_name ( object_id ) AS TableName FROM sys.dm_db_partition_stats WHERE index_id=0 OR index_id=1 GROUP BY object_id ORDER BY sum ( used_page_count ) DESC;