Complete a Server Assessment – Index Information

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.object_id
, AS ‘index_name’ 
,ds.type_desc ASfilegroup_or_partition_scheme’ 
, 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.