From Ryan Dunn http://dunnry.com/blog/CalculatingTheSizeOfYourSQLAzureDatabase.aspx

select    
      sum(reserved_page_count) * 8.0 / 1024 [SizeInMB]
from    
      sys.dm_db_partition_stats

GO

select    
      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 [SizeInMB]
from    
      sys.dm_db_partition_stats, sys.objects
where    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id

group by sys.objects.name
order by sum(reserved_page_count) DESC

The first one will give you the size of your database in MB and the second one will do the same, but break it out for each object in your database ordered by largest to smallest.

Here is a query which will give you by table the total size, number of rows and bytes per row:

select 
    o.name, 
    max(s.row_count) AS  Rows ,
    sum(s.reserved_page_count) * 8.0 / (1024 * 1024) as  GB ,
    (8 * 1024 * sum(s.reserved_page_count)) / (max(s.row_count)) as  Bytes/Row 
from sys.dm_db_partition_stats s, sys.objects o
where o.object_id = s.object_id
group by o.name
having max(s.row_count) > 0
order by GB desc

And here is a query which is the same as above but breaks it down by index:

select  
    o.Name,
    i.Name,
    max(s.row_count) AS  Rows ,
    sum(s.reserved_page_count) * 8.0 / (1024 * 1024) as  GB ,
    (8 * 1024* sum(s.reserved_page_count)) / max(s.row_count) as  Bytes/Row 
from 
    sys.dm_db_partition_stats s, 
    sys.indexes i, 
    sys.objects o
where 
    s.object_id = i.object_id
    and s.index_id = i.index_id
    and s.index_id >0
    and i.object_id = o.object_id
group by i.Name, o.Name
having SUM(s.row_count) > 0
order by GB desc

This way you can have the bigger on top:

 SELECT  sys.objects.name,
            SUM(row_count) AS  Row Count ,
            SUM(reserved_page_count) * 8.0 / 1024 AS  Table Size (MB) 
    FROM sys.dm_db_partition_stats, sys.objects
    WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
    GROUP BY sys.objects.name
    ORDER BY [Table Size (MB)] DESC

Source