SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
        DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH,
       NULL AS stored_len 
   INTO  #temp  
   FROM   INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN('varchar','char','nchar','nvarchar')

SELECT 
'UPDATE #temp
SET stored_len =(SELECT MAX(LEN('+column_name+')) FROM '+table_name+')
WHERE table_name='''+table_name+'''
AND COLUMN_NAME ='''+column_name+''''
FROM #temp

Run this in your database context and execute the final select statement generated, this provide you the list of table and the max lenght of characters stored in it in the #temp table.

Edit:1 I think your question is to run this in all database in a server. I will modify my solution according to that.

 SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
        DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH,
       NULL AS stored_len 
   INTO  #temp  
   FROM   INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN('varchar','char','nchar','nvarchar')

SELECT 
'UPDATE #temp
SET stored_len =(SELECT MAX(LEN('+column_name+')) FROM '+table_name+')
WHERE table_name='''+table_name+'''
AND COLUMN_NAME ='''+column_name+''''
FROM #temp

Run this in your database context and execute the final select statement generated, this provide you the list of table and the max lenght of characters stored in it in the #temp table.

part of the answer is copied from the this snippet

Edit:1 I think your question is to run this in all database in a server. I will modify my solution according to that.