Tables and Data Space Used

Run the query below in SSMS to view the tables and their Data plus Index data space used:

CODE

USE DB_name
GO

SELECT schema_name(Tab.schema_id) + '.' + Tab.name AS TableName /* Alloc.type_desc, */
    ,FORMAT(cast(sum(Alloc.used_pages * 8) AS NUMERIC(36,0)),'#,0') AS DataPlusIndex_KB
    ,FORMAT(cast(sum(Alloc.total_pages * 8) AS NUMERIC(36,0)),'#,0') AS Allocated_KB
    /* ,FORMAT(cast(sum(Alloc.data_pages * 8) AS NUMERIC(36,0)),'#,0') AS NoLOB_DATA_KB   */
FROM sys.tables Tab
    INNER JOIN sys.indexes Indx 
        ON Tab.object_id = Indx.object_id
    INNER JOIN sys.partitions Ptions 
        ON Indx.object_id = Ptions.object_id and Indx.index_id = Ptions.index_id
    INNER JOIN sys.allocation_units Alloc
        ON Ptions.partition_id = Alloc.container_id
WHERE Alloc.used_pages >0 /* and schema_name(Tab.schema_id)='SchemaName' */
GROUP BY schema_name(Tab.schema_id) + '.' + Tab.name /* ,Alloc.type_desc  */
ORDER BY sum(Alloc.used_pages) DESC
GO

Remove comments around type_desc to view DROPPED, IN_ROW_DATA, LOB_DATA, ROW_OVERFLOW_DATA.

Output ex.: