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')
