Get a record or rowcount for all tables in a database

Received a request for a list of our tables and how many records each table had in it. 

In a situation like this you would want to avoid using the following logic as it actually goes through and counts each individual row.

SELECT count(*) FROM TableA

 

Instead using the system tables you can get an accurate and extremely fast list:

select t.name as TableName

     , i.rows as Records

    from     sysobjects as t

        JOIN sysindexes as i

            on i.id = t.id

    where t.xtype = 'U' and i.indid in (0,1)

order by TableName

This query works for SQL Server 2000 and greater.