Getting Size of All Tables in The Database

Here's a quick way of getting the size of all tables in the database. This will display the size in pages, bytes and the number of rows.

select 
   o.name,
   reservedpages = sum(a.total_pages),
   usedpages = sum(a.used_pages),
   pages = sum(case 
                  when a.type <> 1 then a.used_pages
                  when p.index_id < 2 then a.data_pages 
                  else 0 
               end),
   bytes = SUM(a.used_pages)*8096,
   rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)
from sys.objects o
   inner join sys.partitions p on p.object_id = o.object_id
   inner  join sys.allocation_units a on p.partition_id = a.container_id
where o.type = 'U' 
group by o.name
order by 3 desc 

Here's the result set:

name           reservedpages  usedpages  pages   bytes  rows
-------------- -------------- ---------- ------- ------ ------
MyTable        4              4          2       32384  176
MyOtherTable   2              2          1       16192  30

Here's another way of doing it.

SELECT
   TableName = QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name), 
   Rows = SUM(p.Rows) 
FROM sys.objects AS o
   INNER JOIN sys.partitions AS p ON o.object_id = p.object_id
WHERE o.type = 'U'
      AND o.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered      
GROUP BY o.schema_id, o.name
ORDER BY [TableName]

Here's the result set:

TableName                            Rows
------------------------------------ ------
[dbo].[Table1]                          176
[dbo].[Table2]                           30

And to find the size of a specific table

SELECT
   TableName = QUOTENAME(o.name), 
   Rows = SUM(p.Rows) 
FROM tempdb.sys.objects AS o
   INNER JOIN tempdb.sys.partitions AS p ON o.object_id = p.object_id and
                                    o.object_id = object_id('[TableName]')
WHERE o.type = 'U'
      AND o.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered      
GROUP BY o.schema_id, o.name

As always, comments are welcome.


~~ CK