How can I tell if an index contains a column of type varchar(max)?

Question

I'm working on my MSSQL index defragmentation script. Certain kinds of indexes can be rebuilt online, and other kinds can't.

For clustered indexes, it's easy enough to see if the table contains any LOB columns, but for a non-clustered index I need to specifically know if there is any LOB columns covered by that specific index.

I used to be able to do this by looking at the alloc_unit_type_desc in dm_db_index_physical_stats, but this doesn't work for columns of type varchar(max) and xml.

This isn't for my database, so I don't want to get into a discussion over whether or not the index is appropriate, let's just accept that it exists and that I'd like the script to be able to handle this situation.

Does anyone know what kind of SQL I can write to check for this? Assume I have all the relevant object ids and object names in scalar variables.

Answer 1

I think that for "max" columns, the length or size feild in the sys.columns table should be -1. Don't have the documentation in front of me, but let me know if this works.

Answer 2

If you have a char or nvarchar with a max length, then it will have an entry in the sys.columns table with the appropriate system type id for the field, with -1 as the max length.

So, if you want to find all the ids of all the indexes that have a varchar (system type id 167), you would do this:

select distinct
    si.*
from
    sys.indexes as si
        inner join sys.index_columns as ic on
            ic.object_id = si.object_id and
            ic.index_id = si.index_id
            inner join sys.columns as sc on
                sc.object_id = ic.object_id and
                sc.column_id = ic.column_id
where
    sc.system_type_id = 167 and
    sc.max_length = -1