Find number of columns in Db2 database

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


select columns,
    cast(1.0 * columns / tables as decimal (14,2)) as average_column_count
(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 - 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


  • 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.