SQL Server LOB columns

LOB columns have certain restrictions such as the inability to perform online index rebuild. Identifying them maybe important for planning maintenance plans.


SELECT TOP 50 OBJECT_NAME(ic.object_id), OBJECT_NAME(ic.index_id), *
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml
OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
AND max_length = -1)
OR (c.user_type_id IN (129,130)))