Get approximate counts of all tables

Observed sign or problem

We want to quickly recognize which tables are empty, which have thousands of rows, and which dominate the volume of the database. Running COUNT(*) on each table may be unnecessarily expensive for a first scan.

What we need to distinguish

A partition is one storage portion of a table or index. The estimate adds its internal counts without reading every table row.

We look for an estimate obtained from internal partition statistics. In order not to count the same row once for each index, we will only add the heap or clustered index of each table.

A heap is a table without a clustered index. In a table with a clustered index, the leaf level of that index contains the rows of the table; A nonclustered index is a separate structure that retains its own entries and locators to those rows. That is why we should not add all the indices as if each one represented different data.

Query

SELECT
    s.name AS Esquema,
    t.name AS Tabla,
    SUM(COALESCE(ps.row_count, 0)) AS FilasAproximadas
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_partition_stats AS ps
    ON ps.object_id = t.object_id
   AND ps.index_id IN (0, 1)
WHERE t.is_ms_shipped = 0
GROUP BY
    s.name,
    t.name
ORDER BY
    FilasAproximadas DESC,
    s.name,
    t.name;

Parameters or permissions

A DMV is a dynamic management view that exposes internal engine state and counters.

The consultation works on the current basis. The dynamic management view (DMV) exposes internal counters that can change while the engine runs. In SQL Server 2019 and earlier versions it typically requires VIEW DATABASE STATE and VIEW DEFINITION. In SQL Server 2022 and later, applicable permissions include VIEW DATABASE PERFORMANCE STATE and VIEW SECURITY DEFINITION.

The counts are based on information stored or maintained internally. We do not lock each table to produce an accurate transactional snapshot.

How to read the result

FilasAproximadas sums the base storage partitions. index_id = 0 represents a heap and index_id = 1, a clustered index. We exclude nonclustered indexes because they contain their own rows and would double the logical count of the table.

A table with zero rows can be new, temporary from a functional perspective, used only in certain periods or truly obsolete. The count does not explain its purpose.

For an exact check of a specific table, when the cost is acceptable, use:

SELECT COUNT_BIG(*) AS FilasExactas
FROM dbo.Pedido;

What this does establish

It allows you to quickly compare the order of magnitude of the tables and find candidates for an investigation of volume, growth or absence of data.

What this still does not establish

A transactional snapshot represents data from one coherent point in time. The isolation level defines which concurrent changes a query can observe while obtaining it.

It does not guarantee an accurate and consistent count across all concurrent transactions. It also does not measure size on disk: a few rows with large columns can take up more space than millions of narrow rows.

Memory-optimized tables and other specialized structures may require specific views to correctly interpret their storage.

Next reasonable check

Measures the space used by each table. If a specific figure determines a decision, run an exact count on that table and consider its cost, concurrency, and isolation level.

Search SQL Server