SQLUSA

Microsoft SQL Server 2005
Database Design Best Practices

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
 
 
SQLUSA.com Home Page