SQL: Where are the indexes located?

\ by

Where are the indexes located?

SELECT  'table_name' = OBJECT_NAME(i.id),
        i.indid,
        'index_name' = i.name,
        i.groupid,
        'filegroup' = f.name,
        'file_name' = d.physical_name,
        'dataspace' = s.name
FROM    sys.sysindexes i,
        sys.filegroups f,
        sys.database_files d,
        sys.data_spaces s
WHERE   OBJECTPROPERTY(i.id, 'IsUserTable') = 1
        AND f.data_space_id = i.groupid
        AND f.data_space_id = d.data_space_id
        AND f.data_space_id = s.data_space_id
        AND i.name LIKE 'ndx%'
ORDER BY f.name,
        OBJECT_NAME(i.id),
        groupid

g

Posted in: Coding, Configuration

Comments are closed.

Post Navigation