tsql: Find varchar or nvarchar(max)

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;

Leave a Comment

Your email address will not be published. Required fields are marked *