The following query will return the result you need. But beware that the column size_of_table_in_MB is the size reserved for each table, and will not include the size reserved for internal tables related to that table. SELECT T.name AS table_name, PS.row_count AS number_of_rows, PS.reserved_page_count * 8 / 1024.0 AS size_of_table_in_MB FROM sys.tables AS T CROSS APPLY (SELECT SUM(CASE WHEN (PS.index_id < 2) THEN PS.row_count ELSE 0 END) AS row_count, SUM(PS.reserved_page_count) AS reserved_page_count FROM sys.dm_db_partition_stats AS PS WHERE PS.object_id = T.object_id) AS PS ORDER BY number_of_rows;
As you can see I used sys.dm_db_partition_stats to get the required information, and you can read about it in Books Online ([url]http://msdn.microsoft.com/en-us/library/ms187737.aspx[/url]) for more details. 
SQL Script
Posted - 2011-01-09 : 19:39:26
