This article is about a script which I wrote to get detailed information about index settings. The script in this article does not show any information about missing indexes or index usage details. The script will only show the information about settings made on an index using CREATE /ALTER INDEX  statements.

Example:

Just for a demonstration of the script, we will make use of the table Person.Address from the AdventureWorks database.

Using system stored procedures SP_HELP and SP_HELPINDEX, we can get only the index_name, index_description and index_keys details.

USE AdventureWorks2012
GO
sp_help 'Person.Address'
GO
sp_helpindex 'Person.Address'
GO



Just for testing purpose I am going to create a NONCLUSTERED filtered index with included columns and then alter the fill factor of the created index .

USE AdventureWorks2012 
GO 
    
CREATE NONCLUSTERED INDEX IX_Address_PostalCode 
ON Person.Address (PostalCode DESC
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID) 
WHERE City = 'Seattle'
GO 
    
ALTER INDEX IX_Address_PostalCode ON Person.Address 
REBUILD WITH (FILLFACTOR = 80);

The below code block will get us the information about settings made on an index using CREATE /ALTER INDEX statements:

USE AdventureWorks2012 
GO 
    
SELECT 
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END [Is_unique], 
    I.type_desc+' INDEX' IndexType, 
    I.name IndexName, 
    Schema_name(T.Schema_id)+'.'+T.name ObjectName,
    KeyColumns, 
    IncludedColumns, 
    I.Filter_definition, 
    CASE WHEN I.is_padded = 1 THEN ' ON ' ELSE ' OFF ' END [PAD_INDEX], 
    I.Fill_factor, 
    ' OFF ' [SORT_IN_TEMPDB] , -- default value  
    CASE WHEN I.ignore_dup_key = 1 THEN ' ON ' ELSE ' OFF ' END [Ignore_dup_key], 
    CASE WHEN ST.no_recompute = 0 THEN ' OFF ' ELSE ' ON ' END [Stats_Recompute], 
    ' OFF ' [DROP_EXISTING] ,-- default value  
    ' OFF ' [ONLINE] , -- default value  
    CASE WHEN I.allow_row_locks = 1 THEN ' ON ' ELSE ' OFF ' END [Allow_row_locks], 
    CASE WHEN I.allow_page_locks = 1 THEN ' ON ' ELSE ' OFF ' END [Allow_page_locks] ,  
    CASE WHEN ST.auto_created = 0 THEN ' Not Automatically Created ' ELSE ' Automatically Created ' END [Statistics_Creation], 
    CASE WHEN I.is_primary_key = 1 THEN 'Yes' ELSE 'NO' END 'Part of PrimaryKey'
    CASE WHEN I.is_unique_constraint = 1 THEN 'Yes' ELSE 'NO' END 'Part of UniqueKey'
    CASE WHEN I.is_disabled = 1 THEN 'Disabled' ELSE 'Enabled' END IndexStatus, 
    CASE WHEN I.Is_hypothetical = 1 THEN 'Yes' ELSE 'NO' END Is_hypothetical, 
    CASE WHEN I.has_filter = 1 THEN 'Yes' ELSE 'NO' END 'Filtered Index'
    DS.name [FilegroupName] 
FROM sys.indexes I 
 JOIN sys.tables T ON T.Object_id = I.Object_id  
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid 
 JOIN (SELECT * FROM (
    SELECT IC2.object_id , IC2.index_id ,
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
    FROM sys.index_columns IC1
    JOIN Sys.columns C 
       ON C.object_id = IC1.object_id 
       AND C.column_id = IC1.column_id 
       AND IC1.is_included_column = 0
    WHERE IC1.object_id = IC2.object_id 
       AND IC1.index_id = IC2.index_id 
    GROUP BY IC1.object_id,C.name,index_id
    ORDER BY MAX(IC1.key_ordinal)
       FOR XML PATH('')), 1, 2, '') KeyColumns 
    FROM sys.index_columns IC2 
    WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id 
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id 
 --JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id 
 LEFT JOIN (SELECT * FROM
    SELECT IC2.object_id , IC2.index_id , 
        STUFF((SELECT ' , ' + C.name 
    FROM sys.index_columns IC1 
    JOIN Sys.columns C  
       ON C.object_id = IC1.object_id  
       AND C.column_id = IC1.column_id  
       AND IC1.is_included_column = 1 
    WHERE IC1.object_id = IC2.object_id  
       AND IC1.index_id = IC2.index_id  
    GROUP BY IC1.object_id,C.name,index_id 
       FOR XML PATH('')), 1, 2, '') IncludedColumns  
   FROM sys.index_columns IC2  
   WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables 
   GROUP BY IC2.object_id ,IC2.index_id) tmp1 
   WHERE IncludedColumns IS NOT NULL ) tmp2  
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id 
WHERE I.Object_id = object_id('Person.Address') --Comment for all tables





Related Reference links:

http://technet.microsoft.com/en-us/library/ms188783.aspx
http://technet.microsoft.com/en-us/library/ms173760.aspx
http://technet.microsoft.com/en-us/library/ms190283.aspx
http://technet.microsoft.com/en-us/library/ms175105.aspx
http://www.microsoft.com/en-in/download/details.aspx?id=722


See Also