create table #t (i int)

EXEC sp_MSForEachTable 'insert into #T select count(*) from ?'

select sum(i) from #t