Getting a SQL Server RowCount Without doing a Table Scan

Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a SELECT count(*) on all of your tables, but this could create a lot of overhead especially for large databases and large tables. If you don't require an exact answer, it isn't necessary to use a SELECT count(*) query on the rows in a table to get the row count.

SQL Server keeps the row count in sysindexes and it can be retrieved there. The key is to select the correct record from sysindexes. Sysindexes is a system table that exists in every database.

SQL Server maintains at least one row in sysindexes for every user table. A few of the most important columns are:

Column	Data Type	Description
id	int	ID of the table referred to by this row
indid	int	See the text that follows...
rowcnt	bigint	Number of rows in the index

The indid column tells us what part of the table structure this row of sysindexes is referring to:

indid value	Description
0	Table data when there is no clustered index
1	Refers to the clustered index
2 - 254	Non-clustered indexes
255	Text or Image data pages

A table will only have an entry in sysindexes with an indid value of for 0 or 1, never both. That's the entry that we're interested in because its rowcnt field gives us the number of rows in the table. Here is a query that shows the table, index and indid from the pubs database:

USE pubs GO SELECT so.[name] as [table name] , CASE WHEN si.indid between 1 and 254 THEN si.[name] ELSE NULL END AS [Index Name] , si.indid FROM sysindexes si INNER JOIN sysobjects so ON si.id = so.id WHERE si.indid < 2 AND so.type = 'U' -- Only User Tables AND so.[name] != 'dtproperties' ORDER BY so.[name]

Here are the results:

table name	Index Name	indid
authors	UPKCL_auidind	1
discounts	NULL	0
employee	employee_ind	1
jobs	PK__jobs__117F9D94	1
pub_info	UPKCL_pubinfo	1
publishers	UPKCL_pubind	1
roysched	NULL	0
sales	UPKCL_sales	1
stores	UPK_storeid	1
titleauthor	UPKCL_taind	1
titles	UPKCL_titleidind	1

As you can see from the results, most of the indexes are clustered (indid=1) but a few tables such as discounts don't have a clustered index (indid=0).

I started this tip with "If you don't need an exact answer..." That's because there are times when rowcnt is not the exact number of records in the table.

This can be corrected by updating statistics on the table with:

dbcc updateusage go

Here's the CREATE FUNCTION script for udf_Tbl_RowCOUNT

CREATE FUNCTION dbo.udf_Tbl_RowCOUNT ( @sTableName sysname -- Table to retrieve Row Count ) RETURNS INT -- Row count of the table, NULL if not found. /* * Returns the row count for a table by examining sysindexes. * This function must be run in the same database as the table. * * Common Usage: SELECT dbo.udf_Tbl_RowCOUNT ('') * Test PRINT 'Test 1 Bad table ' + CASE WHEN SELECT dbo.udf_Tbl_RowCOUNT ('foobar') is NULL THEN 'Worked' ELSE 'Error' END * © Copyright 2002 Andrew Novick http://www.NovickSoftware.com * You may use this function in any of your SQL Server databases * including databases that you sell, so long as they contain * other unrelated database objects. You may not publish this * UDF either in print or electronically. ***************************************************************/ AS BEGIN DECLARE @nRowCount INT -- the rows DECLARE @nObjectID int -- Object ID SET @nObjectID = OBJECT_ID(@sTableName) -- Object might not be found IF @nObjectID is null RETURN NULL SELECT TOP 1 @nRowCount = rows FROM sysindexes WHERE id = @nObjectID AND indid < 2 RETURN @nRowCount END GO GRANT EXECUTE ON [dbo].[udf_Tbl_RowCOUNT] TO PUBLIC GO

Let's use it:

use pubs -- assuming the UDF was created in pubs go SELECT [name] , dbo.udf_Tbl_RowCOUNT ([name]) as [Row Count] FROM sysobjects WHERE type='U' and name != 'dtproperties' ORDER BY [name] GO

Here are the results:

name	Row Count
authors	24
discounts	3
employee	43
jobs	14
pub_info	8
publishers	8
roysched	86
sales	21
stores	6
titleauthor	25
titles	18