Retrieve a list of all indexes along with their file groups

This is a script we use frequently to identify the location of all clustered and non-clustered indexes as part of storage re-arrangement operations. It returns the list of all indexes along with the file group name.

use
go
SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    fg.name AS FileGroupName
FROM sys.indexes i
INNER JOIN sys.filegroups fg on i.data_space_id = fg.data_space_id
    AND i.name IS NOT NULL
INNER JOIN sys.tables t ON i.object_id = t.object_id
/*
TableName  IndexName       IndexType     FileGroupName
---------- --------------- ------------- -------------
Table1     IDX_Table1_Col1 CLUSTERED     PRIMARY
Table1     IDX_Table1_Col2 NONCLUSTERED  NCI
Table2     IDX_Table2_Col1 NONCLUSTERED  NCI
Table2     IDX_Table2_Col2 NONCLUSTERED  NCI
*/