List columns by name length in MySQL database

This query returns columns in all databases (schemas) ordered by their name length.

Query

select col.column_name,
    char_length(col.column_name) as column_name_length,
    col.table_schema as database_name,
    col.table_name
from information_schema.tables as tab
inner join information_schema.columns as col 
    on col.table_schema = tab.table_schema
    and col.table_name = tab.table_name
where 
    tab.table_type = 'BASE TABLE'
    and tab.table_schema not in ('mysql', 'information_schema', 
                                'performance_schema', 'sys')
    -- and tab.table_schema = 'database_name' -- put your database name here
order by char_length(col.column_name) desc,
    col.column_name;

Note: if you need the information for a specific database (schema), then uncomment the table_schema line and provide your database name.

Columns

  • column_name - name of the column
  • column_name_length - length of the column name
  • table_schema - name of the database (schema)
  • table_name - name of the table

Rows

  • One row: represents one column of each table in a database (schema)
  • Scope of rows: each column that exists in a database (schema)
  • Ordered by: length shown in descending order - from longest to shortest

Sample results