Lighting Quick Table RowCounts for your Tables

If you are familiar with TSQL and want to get a row count from your tables you might be tempted to run a simple

Select count(*) from myTable

This however can be a lengthy wait if you have millions and millions of records. Not to mention the potential impact on a production system. Instead consider selecting your rowcounts from your system tables

SELECT OBJECT_NAME(id), ROWS, indid
FROM sysindexes
WHERE indid < 2
AND OBJECT_NAME(id) IN ('myTableName')

Looking forward beyond Sql Server 2005 and beyond the following select should be used and will provide you with the needed information as system tables access may be depracated

SELECT DISTINCT OBJECT_NAME(P.object_id) AS [Name], ROWS FROM sys.indexes I
INNER JOIN sys.partitions P ON P.object_id = I.object_id AND P.index_id = I.index_id
WHERE I.index_id < 2
AND OBJECT_NAME(P.object_id) IN ('myTableName')

So why am I searching for index smaller than 2? In short from the article below you'll find that pure data pages have indid=0, and clustered index pages and the data pages have indid=1, so searching for this information as < 2 covers us. I also took the liberty of adding a distinct. I've not currently read why sometimes there may be duplicate rows, but the objectid of the table may be different and doing a Distinct helps to narrow the results to data you care about.

http://support.microsoft.com/kb/75191