| How to
find filegroup location of indexes? |
|
Execute the following
script in Query Editor to find out which filegroup each index located in.
USE AdventureWorks
GO
SELECT [Object]=s.name+'.'+o.[name]
, ObjectType=o.[type]
, IndexName=i.[name]
, i.[index_id]
, [FileGroup] = f.[name]
, i.data_space_id
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type not in ('IT', 'S')
and index_id != 0
ORDER BY ObjectType, [Object]
GO
|
| The Best SQL Server
2005 Training in the World |
|