Find number of columns in Db2 database

Query returns basic statistics of numbers of columns in a database.

Query

select columns,
    tables,
    cast(1.0 * columns / tables as decimal (14,2)) as average_column_count
from 
(select count(*) as columns,
        count(distinct tab.tabname concat '.' concat tab.tabschema) as tables
    from syscat.tables tab
    inner join syscat.columns col 
        on col.tabschema = tab.tabschema
        and col.tabname = tab.tabname
    where tab.type = 'T'
        and tab.tabschema not like 'SYS%'
) q

Columns

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

Rows

  • Query returns just one row

Sample results