(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB, (a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB, Tested on SQL Server 2008/2012 (Commented a line where is_memory_optimized = 1) SELECT WHERE t.is_ms_shipped = 0 - Not a system tableĮxtension to answer that handled table partitions to get size in MB and GB. SELECT p., p., total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages) SELECT, index_id, partition_count=count(*), =sum(), data_compression_cnt=count(distinct ) JOIN sys.indexes i ON t.object_id = i.object_id JOIN sys.tables t ON s.schema_id = t.schema_id

, = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'ĮLSE ( SELECT DISTINCT p.data_compression_desc END - 0=Heap 1=Clustered 5=Clustered Columnstore The following query addresses all of those issues. Several suggested fixes don't address the core problem or are simply wrong as well. It also doesn't distinguish between the size of the data and indexes, which is often very relevant. Marc_s' answer gives incorrect results when working with multiple partitions and/or filtered indexes. SUBSTRING(index_size, 0, LEN(index_size)-2), Insert into #ConvertedSizes (Name,, reservedKb, dataKb, reservedIndexSize, reservedUnused) The following script will generate the information I'm looking for.

However, the results returned by the stored procedure are not sortable, since the columns are character values. If provided with a table name, it returns the space used by that table. There is a handy stored procedure named sp_spaceused that will return all of the space used by the database. After some searching, I could not find an easy way to get information on all of the tables.

Wise disk cleaner 9