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

Large objects in MariaDB 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.

Query

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;

Columns

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

Rows

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

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