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’ve come back to more than once. 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] ,DATA_TYPE ,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE 1 = 1 AND DATA_TYPE IN ( 'varchar', 'nvarchar' ) AND CHARACTER_MAXIMUM_LENGTH = -1;