Find What Filegroup a Given Index is on.

If you use the SQL Server system function sp_helpindex, you'll get back table information as well as index information, which includes a concatenated string which tells you what filegroup the index is on. I have an issue right now where we're doing cleanup of tables which had indexes created on the wrong filegroups, so I wanted a way to programmatically determine this information without having to run sp_helpindex on each table. To my surprise, I wasn't really able to find an article online for how to do this. Plenty of articles on moving the indexes to a new filegroup, which were helpful since I have to do that, but not for this initial research stage. With that in mind, I'd like to show you what I came up with.

    select 
        FilegroupName = ds.name, 
        IndexName = i.Name,
        IndexType = i.type_desc,
        TableName = t.Name
    from sys.indexes i
    inner join sys.data_spaces ds
        on i.data_space_id = ds.data_space_id
    inner join sys.tables t
        on i.object_id = t.object_id
    where i.type in (1, 2, 5, 6)

I got this basically by using sp_helptext on sp_helpindex.