Find LOB Columns Script

Scans all tables in a SQL Server database and list the columns which are large objects (TEXT, NTEXT, ,IMAGE VARCHAR(MAX), NVARCHAR(MAX), XML, VARBINARY)

I wanted this as i wanted to identify which tables i couldn’t reindex online

SELECT 
    c.object_id, 
    OBJECT_NAME(c.object_id) as [Object Name], 
    c.name as [Column Name], 
    t.name as [Column Type]
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id 
WHERE c.object_id in 
    (SELECT object_id FROM sys.objects 
     WHERE type_desc = 'USER_TABLE')
AND (t.name in ('image', 'text', 'ntext', 'xml')
OR (t.name in ('VARCHAR', 'NVARCHAR', 'VARBINARY') AND c.max_length = -1))

The same can also be one via information_schema views:

SELECT * 
FROM information_schema.columns
WHERE TABLE_NAME IN 
    (SELECT table_name 
     FROM information_schema.tables 
     WHERE table_type = 'base table')
AND (DATA_TYPE in('text','ntext','xml','image')
OR  (DATA_TYPE in('varchar','nvarchar','varbinary')
AND CHARACTER_MAXIMUM_LENGTH = -1))
ORDER BY TABLE_NAME
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: