Find most popular column names in Vertica database

This query lets you find out most popular column names.

Query

select column_name,
       count(*) as tables,
       round(100 * count(*) / t.alltables,2) as percent_tables
from v_catalog.columns c
join (select count(*) as alltables
      from v_catalog.tables
     ) t on 1=1
group by column_name,
         t.alltables
order by tables desc;

Columns

  • column_name - column name
  • tables - number of tables that have particular column name
  • percent_tables - percentage of tables with column with that 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

Below is a list of most popular column names in VMart database.