SQL: Efficiently Get Table Row Count

A quick and efficient way to get the row count of a table is to query the system table sysindexes

In order to get the rows of a single table called MyTable you can use the following SQL:

SELECT rows FROM sysindexes WHERE indid < 2 AND ID = OBJECT_ID('MyTable')

In order to get the rows of all tables, you can use this:

SELECT so.name, si.rows
FROM sysindexes si 
       INNER JOIN sysobjects so on si.id = so.id
WHERE si.indid < 2 
       AND so.xtype = 'U'
ORDER BY so.name