SQL Script - Check all table sizes in database

Written by Ines

Last published at: July 4th, 2024

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,
  SUM(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
ORDER BY
  UsedSpaceKB DESC;