Skip to main content
Skip table of contents

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

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.