generate dynamically a SQL Union statement

Example in oracle:

select 
  statement || decode(rownum, max(rownum) over (partition by 1),' order by numberOfRows, table_name;',' union all')
from
(
  select 'select '''||table_name||''' table_name, count(1) numberOfRows from ' || table_name as statement
  from user_tables
  order by table_name
);