SQL Server : Get size of all tables in database

Question

The above sql works fine but i want the size in KB OR MB OR GB at the end i want a new column which show total size like TableSizeInMB+IndexSizeInMB KB OR MB OR GB

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName, 
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

thanks

Answer 1

Try:

  ;with cte as (  
  SELECT  
  t.name as TableName,  
  SUM (s.used_page_count) as used_pages_count,  
  SUM (CASE  
              WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
              ELSE lob_used_page_count + row_overflow_used_page_count  
          END) as pages  
  FROM sys.dm_db_partition_stats  AS s   
  JOIN sys.tables AS t ON s.object_id = t.object_id  
  JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id  
  GROUP BY t.name  
  )  
  ,cte2 as(select  
      cte.TableName,   
      (cte.pages * 8.) as TableSizeInKB,   
      ((CASE WHEN cte.used_pages_count > cte.pages   
                  THEN cte.used_pages_count - cte.pages  
                  ELSE 0   
            END) * 8.) as IndexSizeInKB  
  from cte  
 )  
 select TableName,TableSizeInKB,IndexSizeInKB,  
 case when (TableSizeInKB+IndexSizeInKB)>1024*1024   
 then cast((TableSizeInKB+IndexSizeInKB)/1024*1024 as varchar)+'GB'  
 when (TableSizeInKB+IndexSizeInKB)>1024   
 then cast((TableSizeInKB+IndexSizeInKB)/1024 as varchar)+'MB'  
 else cast((TableSizeInKB+IndexSizeInKB) as varchar)+'KB' end [TableSizeIn+IndexSizeIn]  
 from cte2  
 order by 2 desc  

Answer 2

Maybe you want this formatting of total size:

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
), 
cte2 as
(
select
    cte.TableName, 
    cast((cte.pages * 8.) as decimal(10,3)) as TableSizeInKB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8.) as decimal(10,3)) as IndexSizeInKB
from cte
)
select TableName, TableSizeInKB, IndexSizeInKB,
    case 
       when s > 1024 * 1024 then format(s / 1024 / 1024, '0.###'' GB''')
       when s > 1024 then format(s / 1024, '0.###'' MB''')
       else format(s, '0.###'' KB''') end as [TableSize + IndexSize]
from cte2
cross apply (values (TableSizeInKB + IndexSizeInKB)) t(s)
order by s desc

Answer 3

Please also check:

 ;with cte as (
 SELECT
 t.name as TableName,
 SUM (s.used_page_count) as used_pages_count,
 SUM (CASE
             WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
             ELSE lob_used_page_count + row_overflow_used_page_count
         END) as pages
 FROM sys.dm_db_partition_stats  AS s 
 JOIN sys.tables AS t ON s.object_id = t.object_id
 JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
 GROUP BY t.name
 )
 ,cte2 as(select
     cte.TableName, 
     cast((cte.pages * 8.) as decimal(10,3)) as TableSizeInKB, 
     cast(((CASE WHEN cte.used_pages_count > cte.pages 
                 THEN cte.used_pages_count - cte.pages
                 ELSE 0 
           END) * 8.) as decimal(10,3)) as IndexSizeInKB
 from cte
)
select TableName,TableSizeInKB,IndexSizeInKB,
case when (TableSizeInKB+IndexSizeInKB)>1024*1024 
then cast((TableSizeInKB+IndexSizeInKB)/1024*1024 as varchar)+'GB'
when (TableSizeInKB+IndexSizeInKB)>1024 
then cast((TableSizeInKB+IndexSizeInKB)/1024 as varchar)+'MB'
else cast((TableSizeInKB+IndexSizeInKB) as varchar)+'KB' end [TableSizeIn+IndexSizeIn]
from cte2
order by 2 desc

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.