sp_GetRowcount: How to count the number of rows in any SQL Server table fast

Have you ever had to find the number of rows in a user table, and then wrote a little “SELECT COUNT(*) FROM tblWhatever” and hit execute…and waited…and waited…and waited some more? And then started wondering what was going on?

If so, this post is for you.

Let me explain the problem, and then give you a reusable solution. Consider this a turning point in your career.

Grabbing hands

See, the main problem with SELECT COUNT (*) blah blah blah is that SQL Server is going to find the smallest index it can for your table that includes all the records, and then it’s going to read all the records. Not just count, but read! I’m not being insincere. Hello, Index Scan in large amounts.

And that doesn’t even take into account what your transaction isolation level is, and if your little SELECT COUNT(*) ends up blocking some other queries. It can be a real mess, after all.

And that’s not at all what you wanted. What you wanted was just a number, which fortunately for you is stored in a nice Dynamic Management View (DMV) named sys.dm_db_partition_stats. The data you want is already there, but the only problem is you likely have to write a query that joins other DMVs to use it, and that’s much longer than SELECT COUNT(*) blah blah blah.

This is probably why it seems so many folks still frequently use SELECT COUNT(*) blah blah blah.

It’s a competitive world

In a way, this post is an homage to those who’ve gone before me, because I can’t count how many times I’ve either searched for or shared a link for a post from Brent Ozar (written by Jes Schulz) or Pinal Dave on how to query tables faster using the DMVs than SELECT COUNT(*) blah blah blah.

Well, it’s been enough times that I just decided to write my own stored procedure. Here it is:

CREATE OR ALTER PROCEDURE sp_GetRowcount
       @table_name NVARCHAR(128) = NULL
       , @schema_name NVARCHAR(128) = NULL
       , @database_name NVARCHAR(128) = NULL
AS;
 
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET LOCK_TIMEOUT 1000;
 
SELECT @database_name = ISNULL(@database_name, DB_NAME());
 
DECLARE @SQL NVARCHAR(MAX);
 
SET @SQL = N'
SELECT
       [schema] = s.[name]
    , [table] = t.[name]
    , [rows] = SUM(p.row_count)
FROM ' + QUOTENAME(@database_name) + N'.sys.tables t
JOIN ' + QUOTENAME(@database_name) + N'.sys.schemas s
       ON t.schema_id = s.schema_id
JOIN ' + QUOTENAME(@database_name) + N'.sys.dm_db_partition_stats p
       ON t.object_id = p.object_id
    AND t.type_desc = ''USER_TABLE''
    AND p.index_id IN (0,1)
WHERE t.[name] = ' + CASE WHEN @table_name IS NOT NULL
       THEN N'''' + @table_name + N''''
       ELSE N' t.[name]'
       END
       + N'
       AND  s.[name] = ' + CASE WHEN @schema_name IS NOT NULL
       THEN N'''' + @schema_name + N''''
       ELSE N' s.[name]'
       END
       + N'
GROUP BY
       s.[name]
       , t.[name]
ORDER BY
       [rows] DESC';

EXEC sp_executesql @SQL;

Everything counts

It’s fairly straightforward with three parameters, and I set it up to be used from the master database. In the master database it not only can be executed from any database, but also for any database, schema, or table. You can use it for one table, or all tables, and by default it sorts by the most number of rows descending.

Some examples include:

sp_GetRowcount ‘tblWhatever’ – This returns the number of records for any table with that name in the context of your current database. If you have the same named table with multiple schemas, then you get multiple records. You could use the @table_name parameter if you want to, but I put that parameter first since people are busy and probably won’t use this unless it involves less typing than SELECT COUNT(*) blah blah blah.

sp_GetRowcount @schema_name = ‘dbo’ – This returns a list of all tables within a schema, in this case the ‘dbo’ schema. You’d probably use it to query schema’s other than ‘dbo’ though.

sp_GetRowcount @database_name = ‘pubs’ – This returns you a list of all tables in the ‘pubs’ database, which can be handy if you’re trying to find the one’s with the most records.

And of course, you can also use any combination of the three parameters to get whatever results you want. Heck, you can just execute it without parameters in the context of any database and get the row counts for all user tables in that database.

I hope you found this post fun packed, and that it gives you confidence to get your row counts faster!

UPDATE: As suggested by Brent below, I have added his recommended changes in case a table is being altered during the execution of sp_GetRowcount.