SQL Server 2008 r2 table sizes

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) 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]