LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon