MySQL - find columns where type = mediumtext and max(char_length)<255
<p>I have a situation where we have hit max row size due to excessive columns.</p>

<p>I've found a way to list the columns and the max characters for the type of mediumtext which I intend on converting to varchar(200 or smaller) to reduce the footprint.</p>

<p>I would like to further refine this query to just show any mediumtext that is less than 255 characters.</p>

<pre><code>SELECT CONCAT(
GROUP_CONCAT(
CONCAT('(SELECT \'',COLUMN_NAME,'\' AS `column`,MAX(CHAR_LENGTH(`',COLUMN_NAME,'`)) AS `max_length` ','FROM`',TABLE_SCHEMA,'`.`',TABLE_NAME,'` ORDER BY `max_length` DESC LIMIT 1)')
SEPARATOR ' UNION ALL '
), ';'
) AS _SQL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable'
AND COLUMN_TYPE='mediumtext';
</code></pre>

<p>I've been replacing <code>;</code> with <code>order by max_length desc;</code> to get what I need and then eliminate what I don't want. However, I'd like to pretty it up.</p>

<p>This is the output I'm getting right now.</p>

<pre><code>| column                   | max_length |
+--------------------------+------------+
| a_str_7                  |        291 |
| description              |        268 |
| a_str_8                  |        160 |
| a_str_9                  |         93 |
| close_notes              |         46 |
| a_str_6                  |          2 |
| comments                 |       NULL |
| work_notes               |       NULL |
| group_list               |       NULL |
| a_str_10                 |       NULL |
| comments_and_work_notes  |       NULL |
| additional_assignee_list |       NULL |
| user_input               |       NULL |
| a_str_11                 |       NULL |
| work_notes_list          |       NULL |
| approval_history         |       NULL |
| watch_list               |       NULL |
| a_str_12                 |       NULL |
| mgt_only                 |       NULL |
</code></pre>