Check all table sizes in database

Written by Ines

Last published at: February 25th, 2022

Version notice

This query was written and tested on version 7.2. For earlier versions, please contact Support to redefine it.


Query Description

Display the Total amounts of items in each table; total space in KB that they take up, Used Space in KB and Unused Space.


Query Example

Query

SELECT

  t.Name                                       AS TableName,

  s.Name                                       AS SchemaName,

  p.Rows                                       AS RowCounts,

  SUM(a.total_pages) * 8                       AS TotalSpaceKB,

  SUM(a.used_pages) * 8                        AS UsedSpaceKB,

  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

  sys.tables t

  INNER JOIN sys.indexes i ON t.object_id = i.object_id

  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

  LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE

  t.Name NOT LIKE 'dt%'

  AND t.is_ms_shipped = 0

  AND i.object_id > 255

GROUP BY

  t.Name, s.Name, p.Rows, a.used_pages

ORDER BY

  a.used_pages desc