List tables by the number of rows in Db2 database

This query returns list of tables in a database with their number of rows.


select tabschema concat '.' concat tabname as table_name,
    card as rows, 
from syscat.tables
order by card desc


  • table_name - table name with schema name
  • rows - number of rows in table (cardinality) ; -1 if statistics are not collected.
  • stats_time - date and time the statistics were last updated


  • One row represents one table
  • Scope of rows: all tables in a database including tables without rows
  • Ordered by number of rows descending, from largest to smallest (in terms of number of rows)


Number of rows may be not up to date as it's taken from DB2 statistics. Statistics must be updated after tables has had many updates, or after reorganizing any of the tables. You can do it with RUNSTATS command.

You can see when they were last updated for particular table in stats_time column of this query.

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.