DBA Scripts: Get Table Sizes

July 4, 2026

–

Part of the DBA-Tools Project.

Walk onto a new SQL Server instance and the first honest question is usually “where’s all the space going.” Not at the database level, sys.master_files answers that in seconds, but at the table level, inside a database that’s grown for years without anyone tracking which objects are actually driving the size. Backups take longer than they should, an index rebuild window runs over, and nobody can point to why without checking each table by hand.

This script answers it in one pass: the largest tables across every online, multi-user database on the instance, ranked by total size, with data and index space broken out separately.

Why Table Sizes Matter

A handful of tables usually account for most of a database’s footprint, and that concentration matters for several reasons:

Backup and restore time scales with the largest tables, not the database total, a single 50 GB table dominates a backup window far more than fifty 1 GB tables spread evenly.
Index maintenance windows are set by the worst-case table. Rebuilding a clustered index on the biggest table in the database is usually the single longest-running step in a maintenance job.
Unexpected growth in one table, from a missing archival job or a logging table nobody’s purging, is easy to miss until it’s already consuming most of the drive.
Heaps (tables with no clustered index) show up in the same pass, and a heap that’s also large is worth a second look, heaps don’t reclaim space from deletes the way clustered tables do.

Common Symptoms

Backup jobs that take noticeably longer than the database’s total data size would suggest.
An index maintenance job that used to finish comfortably overnight and now runs into business hours.
Disk space alerts with no obvious single cause when checking at the database level.
A sys.dm_db_partition_stats query someone ran once, ad hoc, that never got turned into a repeatable check.

When to Run This Script

Routine SQL Server health checks, especially on an instance you’ve inherited or haven’t audited recently
Getting to know a new or unfamiliar instance for the first time
Investigating a disk space alert or a backup job that’s slowed down
Before planning an index maintenance window, to identify which tables will dominate the runtime

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-TableSizes
Category    : performance
Purpose     : Largest tables across all online user databases by total size (data + index).
              Essential for getting to know a new instance — identifies the major data consumers
              and tables most likely to impact I/O, backup times, and index maintenance windows.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-table-sizes/)
Requires    : VIEW ANY DATABASE, VIEW DATABASE STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

DECLARE @top_per_db INT = 20;  -- top N tables per database

CREATE TABLE #table_sizes (
    database_name    SYSNAME,
    schema_name      SYSNAME,
    table_name       SYSNAME,
    row_count        BIGINT,
    data_size_mb     DECIMAL(12,2),
    index_size_mb    DECIMAL(12,2),
    total_size_mb    DECIMAL(12,2),
    has_heap         BIT
);

DECLARE @db   SYSNAME;
DECLARE @sql  NVARCHAR(MAX);

DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   sys.databases
    WHERE  database_id > 4
      AND  state = 0
      AND  user_access = 0;  -- MULTI_USER only

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'
        INSERT INTO #table_sizes
        SELECT TOP (' + CAST(@top_per_db AS NVARCHAR(10)) + N')
            N' + QUOTENAME(@db, N'''') + N',
            s.name,
            t.name,
            SUM(ps.row_count),
            CAST(SUM(ps.reserved_page_count - ps.used_page_count) * 8.0 / 1024 AS DECIMAL(12,2)),
            CAST(SUM(ps.used_page_count - ps.in_row_data_page_count
                     - ps.lob_used_page_count - ps.row_overflow_used_page_count) * 8.0 / 1024 AS DECIMAL(12,2)),
            CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS DECIMAL(12,2)),
            MAX(CASE WHEN i.type = 0 THEN 1 ELSE 0 END)
        FROM ' + QUOTENAME(@db) + N'.sys.dm_db_partition_stats ps
        JOIN ' + QUOTENAME(@db) + N'.sys.tables  t ON t.object_id = ps.object_id
        JOIN ' + QUOTENAME(@db) + N'.sys.schemas s ON s.schema_id = t.schema_id
        JOIN ' + QUOTENAME(@db) + N'.sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
        WHERE ps.index_id <= 1
          AND t.is_ms_shipped = 0
        GROUP BY s.name, t.name
        ORDER BY SUM(ps.reserved_page_count) DESC;';

    BEGIN TRY
        EXEC sp_executesql @sql;
    END TRY
    BEGIN CATCH
        -- Skip databases that become unavailable mid-run
    END CATCH;

    FETCH NEXT FROM db_cursor INTO @db;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

SELECT
    database_name,
    schema_name,
    table_name,
    row_count,
    data_size_mb,
    index_size_mb,
    total_size_mb,
    CASE has_heap WHEN 1 THEN 'Yes — no clustered index' ELSE 'No' END AS is_heap
FROM #table_sizes
ORDER BY total_size_mb DESC;

DROP TABLE #table_sizes;

This loops every online, multi-user user database, pulls the top N tables per database by reserved space from sys.dm_db_partition_stats, and returns them all ranked together by total size, with data space and index space broken out and a flag for tables with no clustered index.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Find the largest tables across every database on the instance:
.\run.ps1 Get-TableSizes

# To run against a remote sql server:
.\run.ps1 Get-TableSizes -ServerInstance SQLSERVER01

This script lives in the repo at:

Example Output

Real output captured 2026-07-22 09:08 against .. Reformat/trim as needed; screenshot preferred for the live post.

Understanding the Results

total_size_mb is what ranks the list, data_size_mb plus index_size_mb. On a table with several nonclustered indexes, index space can rival or exceed the data itself.
data_size_mb vs index_size_mb tells you where to focus. A table dominated by index space is a candidate for an index review, not more disk, some of that space may be duplicate or unused indexes.
is_heap flags tables with no clustered index. Heaps don’t reclaim space from deletes the way a clustered index does, and they’re more prone to fragmentation over time, worth checking whether it was a deliberate design choice.
row_count next to size gives a quick sanity check, a table with a huge size and modest row count usually means wide rows, LOB data, or heavy index overhead.

Common Causes

Large tables usually get that way for one of a few repeatable reasons: a logging or audit table with no purge job, a staging or migration table left behind after a one-time load finished, or a genuinely core business table that’s simply grown with the business and was never partitioned or archived. The lab output above shows the pattern clearly, several migdb_* tables sitting at nearly identical sizes are leftover migration artifacts, exactly the kind of thing this script is good at surfacing so someone can decide whether they’re still needed.

Best Practices

Run this script on any instance you’re auditing for the first time, table size is one of the fastest ways to understand what an instance actually does.
If a table is large because of unbounded history (logs, audit trails, staging data), pair it with an archiving or purge job rather than just adding disk.
Treat a large heap as a flag to investigate, not necessarily a problem, some heaps are a deliberate design choice for bulk-load workloads, but many are just a missing clustered index.
Re-run periodically and compare, a table growing unexpectedly between runs is often the earliest signal of a missing cleanup job.

Related Scripts

You may also find these scripts useful:

Summary

Table size doesn’t get its own alert in most monitoring setups, it’s not a threshold that fires or a wait type that spikes. It just quietly determines how long your backups take, how long your index maintenance runs, and how much of your disk budget one bad decision three years ago is still costing you.

Running this script as part of a routine health check, or the first thing you do on an instance you’ve just inherited, turns “where’s the space going” from a guessing exercise into a two-minute answer.

Performance

Blog Categories

Comments

Leave a Reply Cancel reply