How to Get Rowcount of Every Table in SSMS? – Interview Question of the Week #288

Question: Can I list each table’s row count in SSMS without running COUNT(*) against every table? Yes. Select the database’s Tables folder, open Object Explorer Details with F7, and enable its Row Count column.

After my video about a quick table row count, readers asked how to view all the tables together. This is the SSMS shortcut I showed them. It is much less clicking than opening every table’s Properties window.

The three steps

1. In Object Explorer, select the Tables folder of the intended database.
2. Press F7, or use View, Object Explorer Details.
3. Right-click a column header in that details pane and check Row Count. Sort that column if you want the largest counts first.

Use the count for the right question

Refresh the pane when you need current metadata, and check whether a filter is hiding tables. For a quick inventory, metadata counts are useful. Don’t assume this UI view is an exact transactionally consistent count of rows satisfying an application predicate.

The equivalent kind of metadata inventory can be scripted. The following sums only heap or clustered-index partitions so nonclustered indexes don’t multiply the count:

-- Run in the database you want to inventory.
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
       SUM(p.row_count) AS approximate_rows
FROM sys.tables AS t
JOIN sys.dm_db_partition_stats AS p ON p.object_id = t.object_id
WHERE p.index_id IN (0, 1)
GROUP BY t.schema_id, t.name
ORDER BY schema_name, table_name;

row_count in this DMV is explicitly approximate. On SQL Server 2022 and later, its documented permissions are VIEW DATABASE PERFORMANCE STATE and VIEW SECURITY DEFINITION. Earlier versions use VIEW DATABASE STATE and VIEW DEFINITION.

For a single table, right-click it, select Properties and look at Storage. The original screenshot shows Orders with 73,595 rows:

I avoid an unnecessary full COUNT(*) merely to browse table sizes. If you need an exact count for a report or a WHERE predicate, use COUNT_BIG with the required query and isolation semantics. Metadata isn’t a substitute for that answer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.