Sometimes there is a need to get the record count of every table in a SQL Server database. The common method used for achieving this is doing a select count(*) from table_namebut this can create a lot of overhead in large tables present in large databases.
The record count of every user table is maintained in the sysindexes system table . There is a indid column in the sysindexes table and every user table will have an entry in sysindexes with indid value of either 0 or 1 , but not both. The rowcnt column corresponding to indid 0 or 1 gives us the total row count of a table.
The User Defined Function given below shows how to retrieve row count for a table by making use of sysindexes.
CREATE FUNCTION dbo.table_row_count (
@sTableName sysname -- Table to retrieve Row Count
RETURNS INT -- Row count of the table, NULL if not found.
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
WHERE id = @nObjectID AND indid < 2