space used by tables

;WITH
space_cte AS
(
          SELECT t.NAME "TableName"
               , p.rows "RowCounts"
               , SUM(a.total_pages) * 8 "TotalSpaceKB"
               , SUM(a.used_pages) * 8 "UsedSpaceKB"
               , (SUM(a.total_pages) - SUM(a.used_pages)) * 8 "UnusedSpaceKB"
            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 
             AND i.index_id = p.index_id
      INNER JOIN sys.allocation_units a 
              ON p.partition_id = a.container_id
           WHERE t.NAME NOT LIKE 'dt%' 
             AND t.is_ms_shipped = 0
             AND i.OBJECT_ID > 255 
        GROUP BY t.Name
               , p.Rows
)
          SELECT A.TableName
               , A.RowCounts
               , A.TotalSpaceKB
               , ( case when A.UsedSpaceKB > 1000000 then cast(A.UsedSpaceKB / 1000000 As varchar(30)) + ' GB'
                        when A.UsedSpaceKB > 1000 then cast(A.UsedSpaceKB / 1000 As varchar(30)) + ' MB'
                        else cast(A.UsedSpaceKB as varchar(30)) + ' KB' end ) "UsedSpace"
               , A.UnusedSpaceKB
            FROM space_cte A
        ORDER BY A.UsedSpaceKB desc