Get Row Count of the table

Normally count(*) will be used to query the no of records of a table. But this might consume significant resources if the tables are very big because scanning a large table or index can consume a lot of I/O.

One way to find the number of rows is through sysindexes. The value of sysindexes.indid will always be 0 for a table and 1 for a clustered index. If a table doesn't have a clustered index, its entry in sysindexes will always have an indid value of 0. If a table does have a clustered index, its entry in sysindexes will always have an indid value of 1.

SELECT object_name(id) ,rowcnt FROM sysindexes WHERE indid IN (1,0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1 and object_name(id) = ''