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


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