Datanory Production

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