List table columns in MySQL database

Posted under » MySQL on 19 May 2023

When we want to see all the databases in our server

    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() 
order by tab.table_name,
    col.ordinal_position;

Or if you just want to see one schema 'abc100' and less precision

    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
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') and tab.table_schema = 'abc100'
    -- uncomment line below for current database only
    -- and tab.table_schema = database() 
order by tab.table_name,
    col.ordinal_position;

You can still use the good ol' describe method

mysql> DESCRIBE abc100;
+---------+-------------+------+-----+---------+
| Field   | Type        | Null | Key | Default |
+---------+-------------+------+-----+---------+
| name    | varchar(20) | YES  |     | NULL    |
| owner   | varchar(20) | YES  |     | NULL    |
| species | varchar(20) | YES  |     | NULL    |
| sex     | char(1)     | YES  |     | NULL    |
| birth   | date        | YES  |     | NULL    |
| death   | date        | YES  |     | NULL    |
+---------+-------------+------+-----+---------+