Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Azure SQL Table Size + Properties
--Table size and properties
SELECT schema_name(t.schema_id) + '.' + t.name AS [table]
,p.[rows]
,cast(sum(spc.used_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS used_mb
,cast(sum(spc.total_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS allocated_mb
,p.index_id
,p.data_compression_desc AS [Index Data Compression]
,t.create_date
,t.lock_on_bulk_load
,t.lock_escalation_desc
,t.is_memory_optimized
,t.durability_desc
,t.temporal_type_desc
FROM sys.tables t
INNER JOIN sys.indexes ind ON t.object_id = ind.object_id
INNER JOIN sys.partitions p ON ind.object_id = p.object_id
AND ind.index_id = p.index_id
INNER JOIN sys.allocation_units spc ON p.partition_id = spc.container_id
GROUP BY schema_name(t.schema_id) + '.' + t.name
,p.rows
,p.index_id
,p.data_compression_desc
,t.create_date
,t.lock_on_bulk_load
,t.lock_escalation_desc
,t.is_memory_optimized
,t.durability_desc
,t.temporal_type_desc
ORDER BY sum(spc.used_pages) DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment