How to get the data space of a table in Azure sql database

Question

Hi All,

In traditional On Premises SQL Database using SSMS we can right click on the table Properties>Storage and get the Data Space information, how to get the same in Azure SQL Database. Please advice.

Thanks

Answer 1

This query gives size of each table in a given database.

select obj.name, sum(reserved_page_count) * 8.0 as "size in KB" from sys.dm_db_partition_stats part, sys.objects obj where part.object_id = obj.object_id group by obj.name

Answer 2

That one didn't quite work for me but it was a good starting point. Thanks for the idea. My issue was around the fact that my DB has lots of different Schema and the same table name can occur in more than one especially as many of the tables are system versioned.

I also thought it was a good idea to return the row count.

select sch.name + '.' + obj.name AS "Table Name",
       sum(reserved_page_count) * 8.0 as "size in KB",
	   sum(CASE WHEN part.index_id IN( 0,1 ) THEN part.row_count ELSE 0 END) AS "Rows"
from sys.dm_db_partition_stats part
JOIN sys.objects obj ON obj.object_id = part.object_id
JOIN sys.schemas sch ON sch.schema_id = obj.schema_id
group by sch.name + '.' + obj.name;