SQL Server Training | Tutorials | How to's

This following script will give you rows count of all table in current databases.

Declare c cursor for select '['+U.name+'.'+O.Name+']' from sysobjects O Inner join sys.schemas U on O.UID=u.Schema_ID where type='U' and O.name <>'dtproperties' 
Declare @name varchar(255),@sql varchar(100),@id int
open c
fetch next from c into @name
Create table #TSpace(ID int identity(1,1),name varchar(255),rows int,resevered varchar(20),data varchar(20),index_size varchar(20),unused varchar(20))
while @@fetch_status = 0
begin
 set @sql = 'sp_spaceused ' +@name

 insert into #Tspace Exec(@sql)
 set @id = @@identity
 update #Tspace set name =@name where id =@id   
 fetch next from c into @name
end
close c
deallocate c
select name,rows from #Tspace order by 2 desc
drop table #tspace

OUTPUT Screenshot

Note: This script will work on SQL Server 2005+ versions.