You may want to look at the Dynamic SQL section in the documentation of your database server, if you want to dynamically construct the list of tables.
SQL 92 includes Dynamic SQL features, and requires them from Intermediate level-conforming implementations.
You may also want to look at the Information schema, to help you select which tables you want, also part of Intermediate SQL implementations.

If you can statically name the tables, here is what comes to mind:
[sql]
select max(char_length(mcurrentstates)) from (select mcurrentstates from planprogress union select mcurrentstates from planmission union select mcurrentstates from planwhatever) t;[/sql]