用于查找LOB列的SQL Server脚本

 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)

更新

试试这个

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存储为varbinary(max)

这将只捕获varbinary(max),而不是varbinary(20),例如,如果您也需要它,那么将varbinary移动到第一个条件,如下所示

 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