SA Developer .NET

Anyone know how to do this in TSQL without dropping and recreating the whole table?

I have a table with an NTEXT column that I plan to change to an NVARCHAR(MAX) and I want to ensure its on a particular filegroup. For the life of me I cannot figure out how to do that. The changing of the data type is simple enough but the optional moving to another filegroup isn't so easy it seems.

Another related issue is how do I detect which filegroup a column is currently on? I've been able to use the [sys].[data_spaces] view to work out where an index or clustered key is stored, but I'm at a loss determining where the column data is stored. Hmmm.

Anyone know how? Remember, TSQL only.

I regret to say, it isn't possible. The location of the lob data cannot be changed after the table has been created. I had exactly that problem at the former company with 42 tables. Solution - create, copy, drop. Great fun over a weekend. Not.

As for where the data is, that is possible, though a lot more difficult than might be expected.

select OBJECT_NAME(object_id) as TableName, au.type_desc as DataType,  ds.name as FileGroupName
    from sys.allocation_units au
        inner join sys.partitions p on au.allocation_unit_id = p.partition_id
        inner join sys.data_spaces ds on au.data_space_id = ds.data_space_id

There are three possible values for  DataType - In-row data, LOB data and row-overflow. The partition listed for LOB data is where your text/ntext/image/varchar(max)/nvarchar(max)/varbinary(max) data is.