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