How to Count the Number of Rows for User and System Tables of a Database · DpkDB
How to Count the Number of Rows for User and System Tables of a Database
Get row counts for every user and system table in a SQL Server database instantly from catalog metadata, and know when you need an exact COUNT instead.
To list every user table in a SQL Server database with its row count, without scanning any of them, read the counts from metadata:
SELECT s.name AS schema_name,
t.name AS table_name,
SUM(p.rows) AS row_count
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.partitions AS p ON p.object_id = t.object_id
WHERE p.index_id IN (0, 1)
GROUPBY s.name, t.name
ORDERBY row_count DESC, schema_name, table_name;
(Sample output.) This returns almost instantly however large the tables are, because it reads catalog data instead of the tables. The first row is your largest table by row count.
Why the query looks like this
sys.partitions has one row for every partition of every heap and index. Two details in the query deal with that:
index_id IN (0, 1) keeps only the base structure of each table: 0 is a heap, 1 is a clustered index (rowstore or columnstore). Every disk-based table has exactly one of the two. Nonclustered indexes have index_id 2 and above and hold the same rows again, so including them would count each row once per index.
Error 8152 never said which column. Message 2628 on SQL Server 2019+ (or trace flag 460 on 2016/2017) names it. Then fix the data or the column, and watch for silent truncation.
SUM(p.rows) adds up the partitions of a partitioned table. An unpartitioned table has a single partition, so the sum changes nothing.
Filtering on another range, such as index_id < 5, is a mistake that appears in older scripts: it double-counts tables that have nonclustered indexes.
Including system tables
"System tables" covers several different things in SQL Server, and they are not all in sys.tables:
sys.objects.type
What it is
Examples
U
User table
Your tables. In msdb, tables such as backupset are also type U, flagged is_ms_shipped = 1
S
System base table
sysschobjs, sysrowsets: the hidden tables behind the catalog views. Listed in sys.objects, but you cannot query them directly without the dedicated administrator connection
IT
Internal table
Storage for Service Broker queues, XML and full-text indexes, change tracking
The catalog views themselves (sys.tables, sys.objects and so on) are views, so they have no rows of their own to count.
To count rows for user and system tables together, start from the partition statistics and join to sys.objects:
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
o.name AS table_name,
o.type_desc,
o.is_ms_shipped,
SUM(ps.row_count) AS row_count
FROM sys.dm_db_partition_stats AS ps
JOIN sys.objects AS o ON o.object_id = ps.object_id
WHERE ps.index_id IN (0, 1)
AND o.type IN ('U', 'S', 'IT')
GROUPBY o.schema_id, o.name, o.type_desc, o.is_ms_shipped
ORDERBY row_count DESC;
Drop 'S', 'IT' from the filter and add AND o.is_ms_shipped = 0 to get back to tables you created. Remove the o.type filter altogether and indexed views appear too, since they are stored like tables.
sys.dm_db_partition_stats.row_count and sys.partitions.rows report the same figure. The dynamic management view also exposes page counts, which is useful when you want size as well as rows.
Variations
Total rows in the database, user tables only:
SELECTSUM(p.rows) AS total_rows
FROM sys.partitions AS p
JOIN sys.tables AS t ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1);
One table:
SELECTSUM(rows) AS row_count
FROM sys.partitions
WHERE object_id = OBJECT_ID(N'dbo.Orders')
AND index_id IN (0, 1);
Empty tables only: add HAVING SUM(p.rows) = 0 to the first query.
Rows per partition of a partitioned table:
SELECT partition_number, rowsFROM sys.partitions
WHERE object_id = OBJECT_ID(N'dbo.Orders')
AND index_id IN (0, 1)
ORDERBY partition_number;
How accurate are metadata counts?
Microsoft documents both sys.partitions.rows and sys.dm_db_partition_stats.row_count as approximate. In practice they are maintained as rows are inserted and deleted and are normally exact or very close, with two caveats:
They are not transactionally consistent. While other sessions are inserting or deleting, the figure can include changes that have not committed yet and may still be rolled back.
They can drift, most often in databases upgraded from very old versions. DBCC UPDATEUSAGE (0) WITH COUNT_ROWS; recounts and corrects them for the current database. It scans the tables, so schedule it accordingly.
Use metadata counts for monitoring, capacity planning, finding the big tables and sanity-checking a data load. Do not use them where the number has to be right, such as reconciling a migration.
When you need an exact count
SELECT COUNT_BIG(*) AS row_count
FROM dbo.Orders;
COUNT_BIG returns bigint; plain COUNT(*) returns int and fails with an arithmetic overflow above 2,147,483,647 rows. SQL Server satisfies the query by scanning the smallest index on the table, so the cost grows with table size. Under the default locking READ COMMITTED isolation level the scan takes shared locks and can be blocked by writers. Adding WITH (NOLOCK) avoids the blocking but gives up the exactness you were paying for. If the database uses read committed snapshot isolation, the count is consistent and does not block.
To get exact counts for every user table, generate one statement per table and run them as a single batch. This needs SQL Server 2017 or later for STRING_AGG:
DECLARE@sql nvarchar(max);
SELECT@sql= STRING_AGG(
CAST(N'SELECT N'''+ REPLACE(s.name + N'.'+ t.name, N'''', N'''''')
+ N''' AS table_name, COUNT_BIG(*) AS row_count FROM '+ QUOTENAME(s.name) + N'.'+ QUOTENAME(t.name) AS nvarchar(max)),
N' UNION ALL ')
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id;
SET@sql+= N' ORDER BY row_count DESC;';
EXEC sys.sp_executesql @sql;
QUOTENAME brackets the schema and table names so that names with spaces or reserved words work, and the cast to nvarchar(max) stops STRING_AGG failing at 8,000 bytes. On a large database this reads every table, so run it off-peak.
Permissions
sys.tables, sys.partitions and sys.objects are readable by any user, but they only return objects the caller has some permission on. A low-privileged login can see a shorter list, not an error.
sys.dm_db_partition_stats requires VIEW DATABASE STATE on the database. On SQL Server 2022 and later, VIEW DATABASE PERFORMANCE STATE is enough.
COUNT_BIG(*) requires SELECT on each table.
Common mistakes
Leaving out the index_id filter, or using the wrong one, and reporting a multiple of the real row count.
Using OBJECT_NAME(object_id) alone. It drops the schema, so sales.Orders and archive.Orders look like duplicates. Join to sys.schemas or use OBJECT_SCHEMA_NAME.
Running the query in the wrong database. These views are per database. Check SELECT DB_NAME(); or add a USE statement first.
Looping with sp_MSforeachtable. It is undocumented and unsupported, and far slower than one query against the catalog.
Treating the metadata count as exact in a comparison that matters.
Using SELECT COUNT(*) on every table as a routine health check. On large tables that is a lot of I/O for a number the catalog already has.
A query-first method: find the expensive queries, read the plan for signs the missing index DMVs never report, build the index online safely, then verify and roll back.
