Tuesday, August 9, 2011

Query to Find index FileGroup location

select distinct
        idx.name as IndexName
        ,object_name(idx.id) as TableName
        ,fil.name as FileGroup
        ,dbf.physical_name as PhysicalFileName
    from sys.tables tb
         inner join sys.sysindexes idx
         on idx.id = tb.object_id
         and tb.type = 'U' and idx.name is not null
         inner join sys.filegroups fil
         on fil.data_space_id = idx.groupid
         inner join sys.database_files dbf
         on fil.data_space_id = dbf.data_space_id
         inner join sys.data_spaces dsp
         on fil.data_space_id = dsp.data_space_id

No comments:

Post a Comment