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

sql sybase sqlanywhere
maf-soft picture maf-soft · Oct 29, 2013 · Viewed 14.9k times · Source

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

Answer

maf-soft picture maf-soft · Oct 29, 2013

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.

Related questions

Getdate(), -1 day

I do not understand why, but somehow this query doesn't work. I want to take system date -1 day where the sysdate is smaller by 1 day then current date. WHERE a.SEND_Date >= dateadd(DD,-1,(CAST(getdate() as …

Read More
How to delete duplicate rows in sybase, when you have no unique key?

Yes, you can find similar questions numerous times, but: the most elegant solutions posted here, work for SQL Server, but not for Sybase (in my case Sybase Anywhere 11). I have even found some Sybase-related questions marked as duplicates for SQL …

Read More
Convert INT to VARCHAR SQL

I am using Sybase and I am doing a select which returns me a column called "iftype", but its type is int and I need to convert into varchar. When I try to do the select without the convert function …

Read More