select
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST (Round(((SUM(a.total_pages)* 8)/1024.00),2) AS NUMERIC(36,2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS USEDSpaceKB,
CAST(Round(((SUM(a.used_pages)*8) /1024.00),2) AS NUMERIC(36,2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST (ROUND(((SUM(a.total_pages) - SUM(a.used_pages))* 8) / 1024.00,2) AS NUMERIC (36,2)) AS UnusdeSpaceMB

FROM sys.tables t
inner join sys.indexes i on t.object_id = i.object_ID
inner join sys.partitions p on i.object_id= p.object_id
inner join sys.allocation_units a on p.partition_id= a.container_id
left outer join sys.schemas s on t.schema_id = s.schema_id

where 
t.NAME NOT LIKE 'dt%'
and t.is_ms_shipped = '0'
and i.OBJECT_ID > 255

group by t.Name, s.Name, p.Rows
order by TotalSpaceMB DESC, t.Name