List file groups and indexes for each tables

This is one of those quick tips and trick posts I do once in a while.  If you ever want to list the indexes and file groups for each table in your sql server database, you can use the following sql for that:

SELECT o.name as TableName, i.name as IndexName,  f.name as FileGroupName
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = ‘U’ ;