What’s the fastest way to get the number of rows in a table in SQL Server 2005 or SQL Server 2008?  Performing a SELECT COUNT(*) on the tables will work but could have a serious performance impact if the tables are of any meaningful size.  Alternatively, a few lines of code hitting one of the system tables can get you the answer with minimal performance impact.  SQL Server consultants use the following script:

SELECT
OBJECT_SCHEMA_NAME(object_id)
,OBJECT_NAME(object_id)
,SUM(Rows) AS NumOfRows –sum the rows if there are multiple partitions
FROM
sys.partitions
WHERE
index_id < 2 –ignore the partitions from non-clustered indexes if any
GROUP BY
OBJECT_ID
ORDER BY
NumOfRows DESC