Find all string (character) columns in MySQL database

String in MySQL are columns with the following data types: char, varchar, binary, varbinary, blob, text, enum, set. The query below lists all columns with string data types.

Query

select col.table_schema as database_name,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type,
       col.character_maximum_length as maximum_length,
       col.character_set_name
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 ('char', 'varchar', 'binary', 'varbinary', 
                        'blob', 'tinyblob', 'mediumblob', 'longblob',
                        'text', 'tinytext', 'mediumtext', 'longtext'
                        'enum', 'set')
      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
  • maximum_length - maximum length in characters
  • character_set_name - character set name

Rows

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

Sample results

0
There are no comments. Click here to write the first comment.