SQL Table Size Query

Below is a SQL query for getting database sizes borrowed from this post. Unfortunately it doesn’t work on Azure SQL databases unless you’ve been granted VIEW_DATABASE_STATE permissions.

select
	schema_name(a.schema_id) + '.' + a.name as [table],
	cast(sum(c.used_pages * 8) / 1024.00 as numeric(36, 2)) as used_mb,
	cast(sum(c.total_pages * 8) / 1024.00 as numeric(36, 2)) as allocated_mb
from
	sys.tables a
		inner join
			sys.indexes b 
				on
					a.object_id = b.object_id
		inner join
			sys.partitions part 
				on
					b.object_id = part.object_id
					and b.index_id = part.index_id
		inner join
			sys.allocation_units c
				on
					part.partition_id = c.container_id
group by
	schema_name(a.schema_id) + '.' + a.name
order by
	sum(c.used_pages) desc