Find most popular column names in Db2 database

This query lets you find out most popular column names.

Query

select c.colname as column_name,
count(*) as tables
from syscat.columns c
inner join syscat.tables t on 
      t.tabschema = c.tabschema and t.tabname = c.tabname
where t.type = 'T'
group by  c.colname
having count(*) > 1
order by count(*) desc;

Columns

  • column_name - column name
  • tables - number of tables that have particular column name

Rows

  • One row represents particular column name (each name appears only once)
  • Scope of rows: all colum names that appear at least twice (in two tables)
  • Ordered by the most popular first

Sample results

0
There are no comments. Click here to write the first comment.