Friday, January 21, 2011

SQL Server: How to Get Physical Path of Tables and Indexes


When database consists of multiple data files and objects (tables/indexes) are dispersed on these multiple data files. Common requirement is to get a list of objects (tables, indexes) along with their physical path.  Here is a simple query to accomplish this task.
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
ORDER BY f.name,
        OBJECT_NAME(i.id),
        groupid

4 comments:

  1. thank you -- this query was exactly what I was looking for. works perfectly to find path on disk that an index resides.

    ReplyDelete
  2. When i move data in filegroup. it will not show particular file group

    ReplyDelete
  3. Very useful. Thank you!

    ReplyDelete
  4. I don't know whether it makes sense, is it possible to find out the amount the space occupied by index in the data file.please advice

    ReplyDelete

All suggestions are welcome