Show All Indexes And Statistics

A simple script to show all the indexes and statistics.

-- Displays list of all indexes and statistics.
DECLARE @sTableName sysname
SET @sTableName = 'YourTableName'

SELECT OBJECT_NAME(id)                    AS 'Table Name',

       CASE 
            WHEN name IS NULL THEN   '< Heap Table >'
            ELSE                      name
            END                           AS 'I or S Name',
            
       CASE INDEXPROPERTY(id, name, 'IsStatistics')
            WHEN 1 THEN 'Statistic'
            ELSE
               CASE INDEXPROPERTY(id, name, 'indexid')
               WHEN 0 THEN '-'
               ELSE 'Index'
               END            
            END                           AS 'I or S ?',

       CASE INDEXPROPERTY(id, name, 'IsStatistics')
            WHEN 1 THEN '-' 
            ELSE
               CASE INDEXPROPERTY(id, name, 'indexid')
               WHEN 0 THEN '-'
               ELSE        CAST(INDEXPROPERTY(id, name, 'indexid') AS sysname)
               END
            END                           AS 'Index ID',
      
       CASE INDEXPROPERTY(id, name, 'IsStatistics')
            WHEN 0 THEN 
                        CASE INDEXPROPERTY(id, name, 'indexid')
                           WHEN 0 THEN '-'
                           WHEN 1 THEN 'Clustered'
                           ELSE        'Non-Clustered'
                        END
            ELSE '-'
            END                           AS 'Index Type',
            
       CASE INDEXPROPERTY(id, name, 'IsUnique')
            WHEN 1 THEN 'Unique'
            ELSE        '-'
            END                           AS 'Unique?'

  FROM sysindexes
 WHERE INDEXPROPERTY(id, name, 'IsHypothetical') = 0
-- AND id = OBJECT_ID(@sTableName)                           -- Restrict by table.
-- AND INDEXPROPERTY(id, name, 'IsStatistics')   = 0         -- 0 = Index, 1 = Statistic
-- AND INDEXPROPERTY(id, name, 'IsUnique')       = 0         -- Restrict by index type.
 ORDER BY 'Table Name',
          'Index Type'