The query below allows you to find the most popular column names in a database (schema).
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;
- 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
- 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