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.

Query

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

Columns

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

Rows

• 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):