We take pride in your success. We let our positivity drive us, day in and out. Talk to us at Mindfire to know us more.

Software Technology Tips

Home » Tips » Database » Get Record Count of a table in SQL Server without doing a Table Scan
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_name but 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.

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

 


Related Tags:
Database

Author: Arijit Rath

top

Database

Let us Connect!

privacy

copyright (c) Mindfire Solutions 2007-2012. Login