Index to Filegroup mapping

Posted on 28. January 2010 by Bennett

Here is a trivial script to show where a particular index resides. It saves clicking around the SSMS GUI.

1
2
3
4
5
6
7

SELECT i.name, i.type_desc, i. is_primary_key, i.is_unique, s.name AS [Filegroup]
	FROM sys.indexes i
	INNER JOIN sys.data_spaces s
	ON i.data_space_id = s.data_space_id
	WHERE i.name IS NOT NULL
	AND i.name NOT IN ('clust', 'clst', 'nc1', 'nc2', 'nc3', 'nc', 'cl')
	ORDER BY s.data_space_id, i.name
This entry was posted in SQL Server, T-SQL. Bookmark the permalink.