SHOW FIELDS but with filter on field type?

Question

I have a mySQL database with a number of tables with many fields.

Is there a native way to find only the fields of the type TEXT?

I know how to do it using a scripting language like PHP. I just want to know whether there is a trick using SHOW TABLES/SHOW FIELDS I am not aware of.

Answer 1

For MySQL 5+ you can query INFORMATION_SCHEMA:

SELECT 
    TABLE_NAME, COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    DATA_TYPE = 'TEXT';