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
thank you -- this query was exactly what I was looking for. works perfectly to find path on disk that an index resides.
ReplyDeleteWhen i move data in filegroup. it will not show particular file group
ReplyDeleteVery useful. Thank you!
ReplyDeleteI 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
ReplyDeleteThank you so much sir. It really helped.
ReplyDelete