select 'insert into #temp select count(*),'''+name+''' from '+object_schema_name(object_id)+'.'+name+';' from sys.objects where type = 'u' 

create table #temp (records int, tablename sysname)
--PUT OUTPUT OF THE TOP LINE IN HERE
select * from #temp order by 1 desc

--for 2005/2008:
Select OBJECT_NAME(object_id) as TableName,SUM(rows) as NumRows,index_id
From sys.partitions p
        Inner Join sys.sysobjects o
                on p.object_id = o.id
Where index_id in (0,1)
        And o.type = 'U'
Group By object_id,index_id
Order By NumRows Desc