List table columns in MySQL database

The query below lists all table columns in all user databases or specific 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',
    -- 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,

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


  • 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


  • 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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.