Objects and Filegroups

A quick map of where your indexes physically live: this query joins sys.indexes to tables, filegroups and files, reporting for each index its name, type, filegroup, and the physical file (and drive letter) that holds it. Useful when you are planning storage layout or checking that indexes landed on the filegroup you expected.

select  distinct t.name 'TableName', 
                i.name 'IndexName', 
                'Index' 'Type',
                i.type_desc,
                fg.name 'FileGroupName',
                f.name 'FileName',
                left(f.filename, 1) 'FileDirectory',
                f.filename 'FilePath'
from    sys.indexes i 
                inner join sys.tables t on t.object_id = i.object_id
                inner join sys.filegroups fg on i.data_space_id = fg.data_space_id
                inner join sys.sysfiles f on f.groupid = fg.data_space_id
order   by t.name, i.name