Find large object (LOB) data type columns in MySQL database

Large objects in MySQL are columns with the following data types: blob, mediumblob, longblob, text, mediumtext, and longtext.

The query below lists all columns with LOB data types.


select tab.table_schema as database_name,
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_schema = 'your database name'
    and tab.table_type = 'BASE TABLE'
    and col.data_type in ('blob', 'mediumblob', 'longblob',
order by tab.table_name,


  • schema_name - name of the database (schema)
  • table_name - name of the table
  • column_name - name of the column
  • data_type - type of data


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

Sample results

List of LOB columns in the Sakila database (schema):

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.