Find LOB Columns Script

Sometime you want to identify the tables which could not reindex online. For this you had to scan all tables in SQL Server database and list the columns which are large objects (VarChar(MAX), NVarChar(MAX), XML, VarBinary, Text, NText, Image).

There are various method to get above information:

Method 1

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 ('VarChar', 'NVarChar', 'VarBinary', 'Text', 'NText', 'Image', 'XML')

AND Character_Maximum_Length = -1

ORDER BY Table_Name

Method 2

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 C.max_length = -1

AND T.Name IN ('VarChar', 'NVarChar', 'VarBinary', 'Text', 'NText', 'Image', 'XML')