Programmatically find indexes that cannot be rebuilt online

I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.

Is there a way to programmatically identify them as part of this statement? So kind of addition to the WHERE predicate

SELECT 
--TOP 20
OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name         [IndexName],  
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed 
INTO #FramentedTableList 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS 
JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID 
JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id =   SI.index_id 
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL 
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL,   @FragmentationThresholdForReorganizeTableLowerLimit) 
ORDER BY avg_fragmentation_in_percent DESC

I use the following to detect LOBs:

SET @LOBCount = (
    SELECT COUNT(*) FROM (
        select t.name as TableName, i.name as IndexName, c.name as ColumnName, ty.name, ic.is_included_column
            from [' + @DatabaseName + '].sys.indexes i 
            INNER JOIN [' + @DatabaseName + '].sys.tables t ON t.object_id = i.object_id 
            inner join [' + @DatabaseName + '].sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
            inner join [' + @DatabaseName + '].sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
            INNER JOIN [' + @DatabaseName + '].sys.types ty ON c.system_type_id = ty.system_type_id
            where 
                t.name = ''' + @TableName + '''  
                AND i.name = ''' + @IndexName + '''
                AND (
                    ty.name IN (''text'', ''ntext'', ''image'')
                    or 
                    (
                        ty.name IN (''varchar'', ''nvarchar'', ''varbinary'', ''xml'') 
                        AND c.max_length = -1
                    )
                )
        ) as x
    )';

Obviously, this is a code fragment from a much larger stored proc that we use to rebuild and/or reorganize indexes. I would recommend you look at Ola Hallengren's scripts at: http://ola.hallengren.com/

StackExchange Database Administrators Q#38684, answer score: 3