Page Comparison

USE DB_name
GO

/* CALCULATES TOTAL DATA SIZE */
SELECT schema_name(Tab.schema_id) + '.' + Tab.name AS TableNameSchemaName /*
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) 
GO

/* CALCULATES DATA SIZE BY TABLE */
SELECT schema_name(Tab.schema_id) + '.' + Tab.name AS TableName /* ,Alloc.type_desc,  */
ORDER   BY ,FORMAT(cast(sum(Alloc.used_pages * 8) DESC
GO

/* Calculates the Total */
SELECT AS NUMERIC(36,0)),'#,0') AS DataPlusIndex_KB
    ,FORMAT(cast(sum(Alloc.usedtotal_pages * 8) AS NUMERIC(36,0)),'#,0') AS DataPlusIndexAllocated_KB
    /*  ,FORMAT(cast(sum(Alloc.totaldata_pages * 8) AS NUMERIC(36,0)),'#,0') AS AllocatedNoLOB_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