Average number of columns per table in Db2 database

Query below returns the average number of columns per table in a database.


select count(distinct c.tabschema || '.' || c.tabname) as tables,
       count(*) as columns,
       dec((count(*)*1.0/count(distinct c.tabschema || '.' || c.tabname)),31,2)
           as avg_column_count
from syscat.columns c
join syscat.tables t on t.tabschema = c.tabschema
                     and t.tabname = c.tabname
where c.tabschema not like 'SYS%'
      and t.type = 'T'


  • tables - number of tables in a database
  • columns - total number of columns in a database
  • avg_column_count - average number of columns in a table


  • Query returns just one row

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.