I have tried hard to google a SQL query to find what objects exist in a specific filegroup, but in the end, I am not satisified with my findings.

I know there is an undocumented SP called sp_ObjectFileGroup, but I do not know how useful this is. My issue is when I create a table with a LOB column, I can put LOB column on a separate filegroup (say SECONDARY) while the other columns on another (say PRIMARY), and then I create a PK on the table. However, when I use sp_ObjectFileGroup, I always get "PRIMARY", meaning the object is on the primary filegroup, but how can I know what is on the SECONDARY filegroup?

I finally decide to rely on myself to figure this out, so after about 2 hours reading BOL, I came up with the following query, which I hope can be of help to my readers.

select fg=filegroup_name(a.data_space_id), tbl=object_name(p.object_id), idx=i.name
from sys.allocation_units a
inner join sys.partitions p
on p.partition_id = a.container_id
and p.object_id > 1024 -- arbitary, just to filter out system objects
and a.type = 2
left join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id
union all
select fg=filegroup_name(a.data_space_id), tbl=object_name(p.object_id), idx=i.name
from sys.allocation_units a
inner join sys.partitions p
on p.hobt_id = a.container_id
and p.object_id > 1024 -- arbitary, just to filter out system objects
and a.type in (1, 3)
left join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id

If you have better way, please share it.