You can use the undocumented sp_foreachtable:

sp_foreachtable ('select ''?'', count (*) from ?')

Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...

Because I need to know the table wich has more records on it! Can you help
me ?

Thanks!

This is a bit of a hack but works for me:

select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name

Since I don't like to use undocumented procedures, since they can go away at
any time, here is the script I use for this

/* Start Script */
Create table #tmpRowCounts (
TblName varchar(128),
RowCt int
)

DECLARE crgetrows CURSOR
FOR select name from sysobjects where xtype = 'U' order by name

DECLARE @tblname varchar(128)
OPEN crgetrows

FETCH NEXT FROM crgetrows INTO @tblname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
 Execute('
 Declare @rowcount int
 select @rowcount = count(*) from ' + @tblname + '
 Insert into #tmpRowCounts values(''' + @tblname + ''',@rowcount)
 ')
END
FETCH NEXT FROM crgetrows INTO @tblname
END

CLOSE crgetrows
DEALLOCATE crgetrows

Select * from #tmpRowCounts

Drop table #tmpRowCounts

GO
/* End Script */

Take your pick.............

select o.name tablename ,i.rows tblrowcount
from sysobjects o
inner join sysindexes i on (o.id = i.id)
where o.xtype = 'U' and o.name <> 'dtproperties'
and i.indid < 2 Order by tablename