Largest tables on disk (SQL Server)

SQL ServerRead onlyScripts Gallery

The tables of the current database that reserve the most space, with their approximate row count.

Verification status. Written from the BroadSQL documentation and the SQL Server documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

Find what fills a database file.
Check the effect of a purge or an archive job.

The Script

Download mssql-largest-tables.sql

-- @description: Largest tables of the current database, reserved space and approximate rows (SQL Server)
-- @tags: sqlserver, diagnostics, size, disk
-- @status: draft
-- @access: read
-- @dialect: SQL Server
-- @param top INTEGER = 20 "Number of tables listed"
SELECT TOP (${top})
       s.name AS schema_name,
       t.name AS table_name,
       SUM(ps.reserved_page_count) * 8 / 1024 AS reserved_mb,
       SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS approximate_rows
  FROM sys.dm_db_partition_stats ps
  JOIN sys.tables t ON t.object_id = ps.object_id
  JOIN sys.schemas s ON s.schema_id = t.schema_id
 GROUP BY s.name, t.name
 ORDER BY reserved_mb DESC;

Parameters

Parameter	Type	Default	Meaning
`top`	`INTEGER`	`20`	Number of tables listed

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/mssql-largest-tables.sql.
Connect to the SQL Server database to inspect, with the VIEW DATABASE STATE and VIEW DEFINITION permissions (VIEW DATABASE PERFORMANCE STATE and VIEW SECURITY DEFINITION from SQL Server 2022).
Check what it declares: LIB SHOW gallery/mssql-largest-tables.sql;
Run it: @gallery/mssql-largest-tables.sql top=10;

What you get

The top tables reserving the most space, in megabytes (8 KB pages), with the rows of their heap or clustered index.

Limits

row_count is approximate, as Microsoft documents it.
Written from the documentation of sys.dm_db_partition_stats.

Found something wrong or unclear on this page? Tell us, with the page address.