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

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

The query below lists all tables that contain columns with LOB data types.


select tab.table_name,
    count(*) as columns
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
        and col.data_type in ('blob', 'mediumblob', 'longblob', 
                               'text', 'mediumtext', 'longtext')
where tab.table_schema = 'your database name'
      and tab.table_type = 'BASE TABLE'
group by tab.table_name
order by tab.table_name;


  • table_name - name of the table
  • columns - number of LOB columns in a table


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

Sample results

List of tables with 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.