How to list all user tables in Sybase along with their number of rows?

Question

I would like to return all the tables and their counter next to it. What is the fastest way to do this?

I know in Oracle, you can do something like below, but not sure about Sybase:

declare n number; begin for rec in (select object_name from user_objects where object_type='TABLE') loop execute immediate 'select count(*) from '||rec.object_name into n; dbms_output.put_line (rec.object_name||':'||n); end loop; end; 

Answer 1

Here is Sybase sql which does the following:

 select ob.name,st.rowcnt from sysobjects ob, systabstats st where ob.type="U" and st.id=ob.id order by ob.name 

Answer 2

It depends on which Sybase product you have in mind. In my SQL Anywhere (9 and 11), your solution does not work, but it works:

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

Answer 3

If the current user is the creator:

 SELECT table_name, count FROM sys.systable WHERE creator = user_id() 

^{NOTE. I am testing this on Sybase ASA 9.}

Answer 4

Since there can be several records in the systabstats table, the query should be:

 select ob.name, sum(st.rowcnt) from sysobjects ob, systabstats st where ob.type="U" and st.id=ob.id group by ob.name order by ob.name 

Answer 5

use the following query

 select name,row_count(db_id(),id) as "Rows" from sysobjects where type='U' order by 2 desc 

Answer 6

select ob.name, st.rowcnt from sysobjects ob, systabstats st where b.type = 'U' and st.id = ob.id and indid = 0 order by ob.name

Answer 7

This works for me using "SQL Central" with SQL Anywhere 17:

 SELECT table_name, st.count FROM systable st WHERE table_type = 'BASE'