I always end up writing this again as I forget to save it!
It generates the SQL that you then need to execute. Advise caution on very large tables.
-- Generate SQL to find Max length of all of a table's text columns:
SELECT
'SELECT ' ||
LISTAGG('MAX(LEN(' || column_name || ') AS max_' || column_name || ' as ' || column_name, ', ') WITHIN GROUP (ORDER BY ordinal_position) ||
' FROM ' || table_schema || '.' || table_name
FROM
SVV_COLUMNS
WHERE
table_schema = 'mySchema' AND table_name= 'myTable'
AND data_type IN ('character', 'character varying')
GROUP BY
table_schema,
table_name
;