The query below lists all table columns in all user databases or specific database.

Query

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;

Note: to view columns just from specific database uncomment one of the clauses above.

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

Sample results

Columns in Sakila sample database:

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free