How to list all the user tables in SQL Anywhere along with their rowcount?

Question

I'd like to list all available tables in my DB, and be able to sort and filter by row count.

Pluvious

Answer 1

That's easy:

select table_name, count
from systable
where primary_root<>0 and creator=1
order by 1

or how about adding the column counts and names?

select t.table_name, t.count rows, count(*) cols,
  list(c.column_name order by c.column_id) col_list
from systable t
left outer join syscolumn c on c.table_id=t.table_id
where t.primary_root<>0 and t.creator=1
group by t.table_name, t.count
order by 1

Hope this helps...

Further information: systable and syscolumn are, since SQL Anywhere 10, only legacy-backwards-compatibility views and Sybase suggests using newer system tables instead... Since I am using version 9 and 11, I stick with these.

Pluvious

note that primary_root<>0 will then not list any tables without a primary key so you could miss some important ones. – Noisette