Select all tables and their row counts

SELECT DISTINCT convert(varchar(30),object_name(i.id)) [Table Name], i.rows FROM sysindexes i INNER JOIN sysobjects o on i.id = o.id