From time to time I need to find all fields in a database that are varchar or nvarchar(max). Following is a little bit of code that I come back to time and again, sharing it for you (and so I can find it next time I need it!) .
SELECT TABLE_NAME AS [Table Name]
,COLUMN_NAME AS [Column Name]
WHERE 1 = 1
AND DATA_TYPE IN ( 'varchar', 'nvarchar' )
AND CHARACTER_MAXIMUM_LENGTH = -1;