question

Use reports. Right click a database, choose reports, then 'Disk usage by Table'. This will give you a report that details for each table, the number of rows and how much disk space is taken up by the table (separated out as data + indexes). If you need to add any more info, or tweak it slightly, capture the SQL that the report uses to generate the data, and build your own table reporting query.

You can do something like this: --- SQL2005 select o.name , reservedpages = sum(a.total_pages) , usedpages = sum(a.used_pages) , pages = sum(case when a.type <> 1 then a.used_pages when p.index_id < 2 then a.data_pages else 0 end) , SUM(a.used_pages)*8096 AS 'Size(B)' , rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end) from sys.objects o join sys.partitions p on p.object_id = o.object_id join sys.allocation_units a on p.partition_id = a.container_id where o.type = 'U' group by o.name order by 3 desc --biggest tables first Modified to show the size of the used pages. Borrowed from here: http://www.dbforums.com/microsoft-sql-server/1003535-query-showing-tablesize-mb.html This should work in SQL 2005 & SQL 2008. Hope this helps!!

Yes, there is a dmv. You can use dm\_db\_partition\_stats ([details][1]) to get this information: SELECT '[' + OBJECT_SCHEMA_NAME([ddps].[object_id]) + '].[' + OBJECT_NAME([ddps].[object_id]) + ']' AS [table_name] , SUM([ddps].[in_row_data_page_count]) AS [in_row_data_page_count] , SUM([ddps].[in_row_used_page_count]) AS [in_row_used_page_count] , SUM([ddps].[in_row_reserved_page_count]) AS [in_row_reserved_page_count] , SUM([ddps].[lob_used_page_count]) AS [lob_used_page_count] , SUM([ddps].[lob_reserved_page_count]) AS [lob_reserved_page_count] , SUM([ddps].[row_overflow_used_page_count]) AS [row_overflow_used_page_count] , SUM([ddps].[row_overflow_reserved_page_count]) AS [row_overflow_reserved_page_count] , SUM([ddps].[used_page_count]) AS [used_page_count] , SUM([ddps].[reserved_page_count]) AS [reserved_page_count] , SUM([ddps].[row_count]) AS [row_count] FROM [sys].[dm_db_partition_stats] AS ddps INNER JOIN [sys].[tables] AS t ON [ddps].[object_id] = [t].[object_id] GROUP BY [ddps].[object_id] [1]: http://msdn.microsoft.com/en-us/library/ms187737.aspx