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 

Author: Sherbaz

Being a gadget enthusiast, He always felt good to try new programming languages and techniques. But never goes in-depth anywhere. www.SplitExpense.in was found when he tried php, html, javascript and mysql. Being an electronics engineer, he is also interested to build small gadgets and tools in embedded and digital electronics. As a profession, he handles microsoft sql server database and calls himself a database administrator

Leave a Reply