lists all table columns in all user databases or specific database.

mysql-schema-all-table-columns-details.txt

Columns

schema_name - database name
table_name - table name
column_id - table column id, starting at 1 for each table
column_name - name of the column
data_type - column data type
max_length - data type max length
precision - data type precision

Rows

One row: represents one table column
Scope of rows: all columns in all tables in all databases or specific database
Ordered by: table name, column id

mysql-schema-all-table-columns.sql

-- https://dataedo.com/kb/query/mysql/list-table-columns-in-database

select tab.table_schema as database_schema,
    tab.table_name as table_name,
    col.ordinal_position as column_id,
    col.column_name as column_name,
    col.data_type as data_type,
    case when col.numeric_precision is not null
        then col.numeric_precision
        else col.character_maximum_length end as max_length,
    case when col.datetime_precision is not null
        then col.datetime_precision
        when col.numeric_scale is not null
        then col.numeric_scale
            else 0 end as 'precision'
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 ('information_schema','mysql',
        'performance_schema','sys')
    -- uncomment line below for current database only
    -- and tab.table_schema = database() 
    -- uncomment line below and provide specific database name
    -- and tab.table_schema = 'your_database_name' 
order by tab.table_name,
    col.ordinal_position;