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.

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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.