That's worked for sys.columns max length, which is correctly showing -1 for those columns.

Thanks.

max length for the varchar(max) and nvarchar(max) columns are 8000.

I was hoping they would be -1.....

When I run John's query against my test table (which contains a VARCHAR(MAX) data type column in it), it returns the data type as 'varchar'.

So it seems to miss out...

Ok thanks to everyone for your help.

Got to the bottom of it.

There were some included columns in the index which were of type varchar(max) which was what was being picked up by SQL Server. Although the...

I will run that script and also get back the data requested by Gail when I'm logged on tomorrow.

I do think maybe the max_lengths are large for those columns which...

I am getting this failure message for my online index rebuild:

An online operation cannot be performed for index 'indexname' because the index contains column '' of data type text, ntext,...

Maybe there was a large amount of data in that nvarchar column which caused sql server to treat it as a LOB during online index rebuilding?

I'm using sql server 2008

Ok thanks.

Still very strange why SQL Server would pick up on nvarchar data type as a LOB data type though.

Thats great, thanks.

will give it a try, thanks.

Ok thanks.

Also in addition to this, is there a way of finding out which index columns are of a certain type?

So for example to bring back all index columns for...

But that would give me all of the indexes for all tables in that database?

I would have to go through each and every index in each table to get to...

Thanks will have a read through it.

That worked thanks.