List columns by name length in MariaDB 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 = 'your database name'
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

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