If you are still interested, I have a query I have been working on (still working on it to get it faster) that will report show you indexes with LOB columns, the size there and the allocation unit type associated with that column. It does not return the same number of columns as Gail's query - but that is likely due to the integration with indexes that I am trying to report on with this one.

Work in progress:

CREATE TABLE #indstats (

indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

,database_id BIGINT

,Object_id BIGINT

,index_id BIGINT

,IndexSizeMB DECIMAL(16,1)

)

INSERT INTO #indstats (database_id,object_id,index_id,IndexSizeMB)

SELECT database_id,object_id,index_id

,CONVERT(DECIMAL(16,1)

,(SUM(ps.avg_record_size_in_bytes * ps.record_count) / (1024.0 * 1024))) AS IndexSizeMB

FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'DETAILED') ps

GROUP BY database_id,object_id,index_id

;

With LOBCols as (

Select FileGroupName = filegroup_name(a.data_space_id)

,TableName = object_name(p.object_id)

,p.object_id

,IndexName = i.name

,ColumnName = c.name

,c.column_id

,LOBUsedPages = a.used_pages

,LOBTotalPages = a.total_pages

,LOBDataSizeMB = a.used_pages * 8/1024

, ps.IndexSizeMB

, (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests

, (us.user_updates) AS UserUpdates

, us.last_user_update AS LastUpdate

, CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL)

/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates

,a.type_desc as AllocUnitType

From sys.allocation_units a

Inner Join sys.partitions p

On p.partition_id = a.container_id

And a.type = 2--LOB data is stored in pages of type Text/Image

Left Outer Join sys.dm_db_index_usage_stats us

On us.object_id = p.object_id

And us.index_id = p.index_id

And us.database_id = db_id()

Left Outer Join #indstats ps

ON us.index_id = ps.index_id

And us.database_id = ps.database_id

And us.object_id = ps.object_id

Left Outer Join sys.indexes i

On i.object_id = p.object_id

And i.index_id = p.index_id

Left Outer Join sys.index_columns ic

On i.index_id = ic.index_id

And i.object_id = ic.object_id

Left Outer Join sys.columns c

On ic.object_id = c.object_id

And ic.column_id = c.column_id

Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0

--And a.data_pages > 0

--And filegroup_name(a.data_space_id) = 'Primary'

Union

Select FileGroupName = filegroup_name(a.data_space_id)

,TableName = object_name(p.object_id)

,p.object_id

,IndexName = i.name

,ColumnName = c.name

,c.column_id

,LOBUsedPages = a.used_pages

,LOBTotalPages = a.total_pages

,LOBDataSizeMB = a.used_pages * 8/1024

, ps.IndexSizeMB

, (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests

, (us.user_updates) AS UserUpdates

, us.last_user_update AS LastUpdate

, CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL)

/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates

,a.type_desc as AllocUnitType

From sys.allocation_units a

Inner Join sys.partitions p

On p.hobt_id = a.container_id

And a.type = 3--Overflow data is stored in pages of type Text/Image

Left Outer Join sys.dm_db_index_usage_stats us

On us.object_id = p.object_id

And us.index_id = p.index_id

And us.database_id = db_id()

Left Outer Join #indstats ps

ON us.index_id = ps.index_id

And us.database_id = ps.database_id

And us.object_id = ps.object_id

Left Outer Join sys.indexes i

On i.object_id = p.object_id

And i.index_id = p.index_id

Left Outer Join sys.index_columns ic

On i.index_id = ic.index_id

And i.object_id = ic.object_id

Left Outer Join sys.columns c

On ic.object_id = c.object_id

And ic.column_id = c.column_id

Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0

--And filegroup_name(a.data_space_id) = 'Primary'

--And a.data_pages > 0

)

Select L.FileGroupName,L.TableName,L.IndexName,L.ColumnName,L.LOBUsedPages,L.LOBTotalPages,L.LOBDataSizeMB,L.IndexSizeMB

,L.UserRequests,L.LastUpdate,L.RatioRequestsToUpdates,L.AllocUnitType,t.name

From LOBCols L

Inner Join sys.columns c

On c.object_id = L.object_id

And c.column_id = L.column_id

Inner Join sys.types t

On t.user_type_id = c.user_type_id

Order By L.TableName asc,L.AllocUnitType