We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SELECT OBJECT_NAME(c.object_id), c.name, t.name 
  FROM sys.columns c 
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
  WHERE t.name IN ('text','ntext','image','xml') 
    OR (t.name IN ('varchar','nvarchar','varbinary') AND c.max_length = -1)

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE in ('text','ntext','image','xml')
	OR (DATA_TYPE in ('varchar','nvarchar','varbinary') AND CHARACTER_MAXIMUM_LENGTH = -1)

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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