SELECT
OBJECT_SCHEMA_NAME(a2.object_id) AS SchemaName,
a2.name AS TableName,
a1.rows as [RowCount],
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) *8) /1024.00/1024.00, 2) ASNUMERIC(36, 2)) AS ReservedSize_GB,
CAST(ROUND(a1.data *8/1024.00/1024.00 , 2) ASNUMERIC(36, 2)) AS DataSize_GB,
CAST(ROUND((CASEWHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE0END) *8/1024.00/1024.00, 2) ASNUMERIC(36, 2)) AS IndexSize_GB,
CAST(ROUND((CASEWHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE0END) *8/1024.00/1024.00 , 2) ASNUMERIC(36, 2)) AS UnusedSize_GB
FROM
(SELECT
ps.object_id,
SUM (CASEWHEN (ps.index_id <2) THEN row_count ELSE0END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASEWHEN (ps.index_id <2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUPBY ps.object_id) AS a1
LEFTOUTERJOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNERJOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUPBY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNERJOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
WHERE a2.type <> N'S'and a2.type <> N'IT'ORDERBY ReservedSize_GB DESC