68

In mssql2005 when I want to get size of table in MBs, I use EXEC sp_spaceused 'table'.

Is there any way to get space used by particular table in SQL Azure using some query or API?

Pat Myron
  • 4,437
  • 2
  • 20
  • 39
user224564
  • 1,313
  • 1
  • 10
  • 14

3 Answers3

157

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.

David Sopko
  • 5,263
  • 2
  • 38
  • 42
Troy Sabin
  • 1,686
  • 1
  • 12
  • 3
  • 2
    This gives a significantly smaller size than sp_spaceUsed on SQL Server (e.g. only 40% or so). Is there a reason for this? If you're using an ODBC connection, how can you tell that it's Azure at the back end? – Grahame Grieve Jun 11 '14 at 13:14
  • this would aggregate different tables with identical names, but different schemas, as one, so in case you use logical separation via schemas but use the same tables, this would yield incorrect results. – user1465073 Sep 26 '22 at 23:18
15

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
tster
  • 17,883
  • 5
  • 53
  • 72
6

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

Fiacc
  • 1,324
  • 1
  • 15
  • 24
d.popov
  • 4,175
  • 1
  • 36
  • 47