Rowcount all tables

declare @tabs TABLE ( id int identity(1,1), tabname sysname)
declare @res TABLE ( tabname sysname, numrows int )

declare @id int, @numrows int, @tabname sysname, @sql nvarchar(4000)

insert into @tabs (tabname)
select name from sysobjects where type = 'U' order by 1 asc

select @id = max(id) from @tabs

while @id > 0
 begin
   select @tabname = tabname from @tabs where id = @id
   set @sql = 'select ''' + @tabname + ''' "tabname", count(1) "numrows" from [' + @tabname + ']'

   insert into @res ( tabname, numrows )
   exec sp_executesql @sql

   set @id = @id - 1
 end

 select tabname, numrows from @res where numrows > 0 order by tabname