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

Query

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'

Columns

• 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

Rows

• Query returns just one row

Sample results