Below you will find the code to get information about the indexes. Simply select the text and copy to the Clipboard.
SELECT OBJECT_NAME(i.object_id) AS ‘ObjectName’
,i.name AS ‘index_name’
,ds.type_desc AS ‘filegroup_or_partition_scheme’
,ds.name AS ‘filegroup_or_partition_scheme_name’
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0
AND OBJECT_NAME(i.object_id) NOT LIKE ‘sys%’
This code was written using the Microsoft SQL Server documentation. If you find that this code was copied from else where, please let me know so I can give proper credit.