question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked ErlandSommarskog commented

SQL Server : Get size of all tables in database

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you add an illustration of what you are looking for?

0 Votes 0 ·
EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered ErlandSommarskog commented

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

114102-image.png






image.png (26.6 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This code successfully ran without any error. thank you so much.

0 Votes 0 ·

case when (TableSizeInKB+IndexSizeInKB)>1024*1024
then cast((TableSizeInKB+IndexSizeInKB)/1024*1024 as varchar)+'GB'

1024 KB means 1 MB and 1024 MB means 1 GB. if so then why compare like this way when (TableSizeInKB+IndexSizeInKB)>1024*1024
please guide me. thanks

0 Votes 0 ·

1024KB*1024KB=1GB.

1 Vote 1 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered TZacks-2728 commented

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


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

when execute the above script then i got this error - Arithmetic overflow error converting numeric to data type numeric.

0 Votes 0 ·

Do you get this error when you execute your original query too?


0 Votes 0 ·

No, i got no error when i execute my original query

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

when execute the above script then i got this error - Arithmetic overflow error converting numeric to data type numeric.

0 Votes 0 ·

Just because you ask a question in a forum and someone gives you an answer, you are not forbidden to try to sort out error messages yourself.

I see two cast in the query. Maybe the types needs to be beefed up there?

0 Votes 0 ·

I ran the code and got the expected output. Are you running the original code?

0 Votes 0 ·