Sql Server : Query all indexes and their filegroups

Quick one today:

Sometimes there is a quick need to list all indexes, both clustered and non-clustered indexes, and their storage location a.k.a. FileGroup.

The below query gives us that ability; As evident, it is expandable to add more details, but here it is kept short to demonstrate the value:

--
-- All all indexes and their FGs
--
SELECT    O.name AS [tableName]
	, I.name AS [IndexName]
	, I.index_id AS [IndexID]
	, I.type_desc AS [IndexType]
	, FG.name  AS [FileGroup_Name]

FROM sys.indexes AS I

INNER JOIN sys.filegroups AS FG
	ON I.data_space_id = FG.data_space_id
INNER JOIN sys.all_objects AS O
	ON I.object_id = O.object_id
	AND O.type = 'U'
	AND O.is_ms_shipped = 0

ORDER BY O.name, I.index_id
GO

 

Hope this helps,
_Sqltimes

 

Read Full Post »