there is no 1 query that will do that.
You can select from sysibm.systablespacestats wich should have a fairly accurate (30 mins delay) of the row count in each tablespace.
You can select from sysibm.systables which has a rowcount at last runstats.
otherwise you will need to generate a lot of seperate selects which you can then execute and union
Following query will have as output a very long select statement that when run will give you your results (replace ? with the schema you want
select substr(replace(replace(cast(xml2clob(xmlagg(xmlelement(NAME Y , col)))
,'<Y>',' UNION ALL ')
from (select 'select '''
|| ''' , '''
|| ''' , count(*) from '
|| strip(name) as col
from sysibm.systables where type not in ('X','P','V') AND creator = ?
I can explain it to you, but i can not understand it for you.