To get all the Table Names and the Rowcount for each Table of a specified DataBase

SELECT
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows)
FROM 
    sysobjects AS so, 
    sysindexes AS si 
WHERE 
    so.xtype = 'U' 
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY 
    1 ASC

The above query will show all the table names and the Rowcount for each table of the selected DataBase in SQL Query Analyzer.