Find most popular column names in MariaDB database

The query below allows you to find the most popular column names in a database (schema).

Query

select column_name,
       count(*) as tables,
       round(100*count(*)/
            (select count(*) from information_schema.tables
             where table_schema not in ('information_schema', 'mysql',
                                        'performance_schema', 'sys')
                   and table_type = 'BASE TABLE'), 2) as percent_tables
from information_schema.columns col
join information_schema.tables tab
     on col.table_schema = tab.table_schema
     and col.table_name = tab.table_name
     and tab.table_type = 'BASE TABLE'
where col.table_schema not in ('information_schema', 'mysql',
                               'performance_schema', 'sys')
group by column_name
order by tables desc;

Columns

  • column_name - name of the column
  • tables - number of tables with that particular column name
  • percent_tables - percentage of tables that have a column with that particular name

Rows

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

Sample results

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