MS SQL Tables and Data Space Used

Run this SQL query in SSMS to view the tables and disk space used (Data plus Index space used).

SQL

USE DB_name
GO

/* CALCULATES TOTAL DATA SIZE */
SELECT schema_name(Tab.schema_id)  AS SchemaName 
    ,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    
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) 
GO

/* CALCULATES DATA SIZE BY TABLE */
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 example:

Search words: disc space, HDD, disc drive, disk drive, data space, SQL script, T-SQL, data base space, db, dw