SELECT s.Name AS SchemaName,

   t.NAME AS TableName,  
   SUM(p.rows)/COUNT(distinct a.type) AS RowCounts,  
   CAST(SUM(a.total_pages) as float) * 8 AS TotalSpaceKB,   
   SUM(a.used_pages) * 8 AS UsedSpaceKB,   
   SUM(a.data_pages) * 8 AS DataSpaceKB,  
   CAST((SUM(a.total_pages) - SUM(a.used_pages)) as float)  * 8 AS UnusedSpaceKB,  
   MIN(FILEGROUP_NAME(a.data_space_id)) as FileGroup  

FROM sys.tables t
JOIN sys.schemas s
ON s.schema_id = t.schema_id
JOIN sys.indexes i
ON t.OBJECT_ID = i.object_id
JOIN sys.partitions p
ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND i.index_id < 2
GROUP BY t.Name, s.Name
ORDER BY TotalSpaceKB desc