SQL Server Tables ordered by Size


SELECT o.name,

       reservedpages = Sum(a.total_pages),

       usedpages = Sum(a.used_pages),

       reservedMB = ( Sum(a.total_pages) * 8 / 1024 ),

       usedMB = ( Sum(a.used_pages) * 8 / 1024 ),

       pages = Sum(CASE

                     WHEN a.type <> 1 THEN a.used_pages

                     WHEN p.index_id < 2 THEN a.data_pages

                     ELSE 0

                   END),

       rows = Sum(CASE

                    WHEN ( p.index_id < 2 )

                         AND ( a.type = 1 ) THEN p.rows

                    ELSE 0

                  END)

FROM   sys.objects o

       JOIN sys.partitions p

         ON p.object_id = o.object_id

       JOIN sys.allocation_units a

         ON p.partition_id = a.container_id

WHERE  o.type = ‘U’

GROUP  BY o.name

ORDER  BY 3 DESC