I'd like to list all available tables in my DB, and be able to sort and filter by row count.
Learn MySQL from scratch for Data Science and Analyticsmysql> SELECT SUM(TABLE_ROWS) ->FROM INFORMATION_SCHEMA. TABLES ->WHERE TABLE_SCHEMA = 'business'; The following table returns the count of records. mysql> SELECT table_name, table_rows ->FROM INFORMATION_SCHEMA.
To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.
To check the count of tables. mysql> SELECT count(*) AS TOTALNUMBEROFTABLES -> FROM INFORMATION_SCHEMA. TABLES -> WHERE TABLE_SCHEMA = 'business'; The following output gives the count of all the tables.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With