ok, this worked pretty well for me;

you can expand this to use char/nchar if needed, but i left it as varchar/nvarchar, as that's all i'd ever use anyway.

no need to check ints or decimals values, the size is the size.

i used FOR XML to build a query, per table, so i can find both the max current length and the current definition.

it produces a query like this(formatted for readbility)

SELECT Max(Len([DatabaseName])) AS [DatabaseName],

'256' AS [CurMaxDatabaseName],

Max(Len([SchemaName])) AS [SchemaName],

'256' AS [CurMaxSchemaName],

Max(Len([ObjectName])) AS [ObjectName],

'256' AS [CurMaxObjectName],

Max(Len([IndexName])) AS [IndexName],

'256' AS [CurMaxIndexName],

Max(Len([StatisticsName])) AS [StatisticsName],

'256' AS [CurMaxStatisticsName],

Max(Len([Command])) AS [Command],

'-1' AS [CurMaxCommand],

Max(Len([CommandType])) AS [CommandType],

'120' AS [CurMaxCommandType],

Max(Len([ErrorMessage])) AS [ErrorMessage],

'-1' AS [CurMaxErrorMessage]

FROM [CommandLog]

and my query generator:

SELECT DISTINCT

schema_name(t.schema_id) As SchemaName,

t.name,

'SELECT ' + sq.Columns + ' FROM ' + schema_name(t.schema_id) + '.' + quotename(name)

FROM sys.tables t

JOIN (

SELECT OBJECT_ID,

Columns = STUFF((SELECT ',MAX(LEN(' + quotename(name) +')) As ' + quotename(name) + ','

+ CASE

WHEN max_length = -1

THEN '''' + convert(varchar,max_length /2) + ''''

WHEN type_name(system_type_id) = ' nvarchar'

THEN '''' + convert(varchar,max_length /2) + ''''

ELSE '''' + convert(varchar,max_length) + ''''

END + ' AS [CurMax' + name + ']'

FROM sys.columns sc

WHERE sc.object_id = s.object_id

AND type_name(system_type_id) IN('varchar','nvarchar')

FOR XML PATH('')),1,1,'')

FROM sys.columns s

) sq ON t.object_id = sq.object_id