Find LOB and binary columns in a SQL Server database
List the user tables that contain text, ntext, image, varbinary or binary columns, using the system catalog views.
This query lists the user tables that contain columns of LOB or binary types: text, ntext, image, varbinary and binary. It reads the column and type metadata from the system catalog.
SELECT o.[name], o.[object_id], c.[object_id], c.[name], t.[name]
FROM sys.all_columns c
INNER JOIN sys.all_objects o
ON c.object_id = o.object_id
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id
WHERE c.system_type_id IN (35, 165, 99, 34, 173)
AND o.[name] NOT LIKE 'sys%'
AND o.[name] <> 'dtproperties'
AND o.[type] = 'U'
GO
System type IDs used
| system_type_id | Type |
|---|---|
| 34 | image |
| 35 | text |
| 99 | ntext |
| 165 | varbinary |
| 173 | binary |
Notes
varchar(max),nvarchar(max)andxmlcolumns have othersystem_type_idvalues and are not returned by this filter.