Find all enum columns in MySQL database

Article for: PostgreSQL MySQL

The query below lists all columns with enum data type in MariaDB database.

Query

select col.table_schema as database_name,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type,
       trim(leading 'enum' from col.column_type) as enum_values
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
where col.data_type in ('enum')
      and col.table_schema not in ('information_schema', 'sys',
                                   'performance_schema', 'mysql')
     --and col.table_schema = 'database_name' -- put your database name here
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Columns

  • database_name - name of the database (schema)
  • table_name - name of the table
  • column_id - column position in table
  • column_name - name of the column
  • data_type - type of data
  • enum_values - declared possible enum values

Rows

  • One row represents one column with a enum type
  • Scope of rows: all columns containing enum data types in the database (schema)
  • Ordered by database name, table name and column position in table

Sample results

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