I use the following query for two reasons:
1. as a quick way to determine row count. Rather than doing select count(columnname) which touches every single row, this looks up the row count from the index.
2. When I want to clear information out of a large test database for local use - this allows me to see what tables have the most information so I know which tables to work on clearing some information out of first.
SELECT rows ,OBJECT_NAME(id), * FROM sysindexes WHERE indid < 2 ORDER BY [rowcnt] DESC