SQL Server script to find LOB columns

Question

Looking for a script to scan all tables in all SQL Server databases and list the columns which are large objects (TEXT, NTEXT, ,IMAGE VARCHAR(MAX), NVARCHAR(MAX), FILESTREAM, XML, VARBINARY).

while I probably can code this myself, I want a ready made script.

Answer 1

 select * from information_schema.columns where data_type in 
    ('TEXT', 'NTEXT','IMAGE' ,'XML', 'VARBINARY')
    or 
    (data_type = 'VARCHAR' and character_maximum_length = -1)
    OR
    (data_type = 'NVARCHAR' and character_maximum_length = -1)

Update Removed FILESTREAM from IN since the data_type is VARBINARY which is already captured

Answer 2

Try this

select * from information_schema.columns
where DATA_TYPE in('text','ntext','xml','image')
or  (DATA_TYPE in('varchar','nvarchar','varbinary')
and CHARACTER_MAXIMUM_LENGTH = -1)
order by DATA_TYPE

filestream is stored as varbinary(max)

This will only capture varbinary(max), not varbinary(20) for example, if you also want that then move varbinary to the first condition, like this

 select * from information_schema.columns
    where DATA_TYPE in('text','ntext','xml','image','varbinary')
    or  (DATA_TYPE in('varchar','nvarchar')
    and CHARACTER_MAXIMUM_LENGTH = -1)
    order by DATA_TYPE