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;