Table Row Count GEM !!!

Use System objects to return the count of records in tables, rather than COUNT(*).

This method is runs almost instantly but may be a little behind in terms of table statistics, i.e. the row counts may be out as statistics are not updated instantly.

SELECT 
      SCHEMA_NAME(o.SCHEMA_ID) SchemaName
    , OBJECT_NAME(o.OBJECT_ID) TableName
    , SUM(ROWS) TableRows 
FROM SYS.PARTITIONS p
INNER JOIN SYS.OBJECTS o 
   ON p.object_id = o.object_id  
WHERE INDEX_ID IN (0,1) 
GROUP BY o.SCHEMA_ID, o.OBJECT_ID 
ORDER BY 1,2,3

Statistics can be updated using DBCC UPDATEUSAGE

Link :
http://furrukhbaig.wordpress.com/2008/07/24/rowcount-for-large-tables/

Nov 2009 Update. Plenty of alternate versions on the web now, linking different system tables >
http://www.bimonkey.com/tag/row-count/
http://www.sqldev.org/transactsql/perform-a-row-count-without-causing-a-scan-60203.shtml