Find identical duplicate indexes

,

The purpose of this query is to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. If you have a need to find indexes having same columns but in any order, set value for the variable @disregard_column_order = 1 in the code.

The query excludes identical indexes where one is clustered and the other one is non-clustered index.  But you can again toggle that by setting value for variable @include_clustered_indexes = 1.


 

/*

whether to include identical indexes where one is clustered and

the other one is non-clustered index

*/ 

DECLARE @include_clustered_indexes bit = 0 
/*

whether to find duplicate indexes where although all columns are same, they may not be in same order

*/ 

DECLARE @disregard_column_order bit = 0 
;WITH cte 
AS 
       (SELECT 
                        o.schema_id, 
                        o.type_desc, 
                        o.object_id, 
                        i.index_id, 
                        i.name index_name, 
                        index_columns=COALESCE((STUFF((SELECTCAST(','+COL_NAME(object_id, column_id)ASvarchar(max)) 
                               FROMsys.index_columns 
                               WHERE (object_id= i.object_idAND index_id = i.index_id) 
                               ORDERBYobject_id, index_id, 
                               CASEWHEN @disregard_column_order = 1 then column_id  else key_ordinal end 
                               FORxmlPATH ('')), 1, 1,'')),''), 
                        i.type_desc index_type, 
                        i.is_unique, 
                        i.data_space_id, 
                        i.ignore_dup_key, 
                        i.is_primary_key, 
                        i.is_unique_constraint, 
                       i.fill_factor, 
                        i.is_padded, 
                        i.is_disabled, 
                        i.is_hypothetical, 
                        i.allow_row_locks, 
                        i.allow_page_locks, 
                        i.has_filter, 
                        i.filter_definition, 
                        i.compression_delay 
       FROMsys.indexes i 
       INNERJOINsys.objects o ON o.object_id= i.object_id 
       WHEREOBJECTPROPERTY(o.object_id,'ismsshipped')= 0 AND index_id != 0 
       AND i.index_id >CASEWHEN @include_clustered_indexes = 1 THEN 0 ELSE 1 END 
--     AND i.index_id != 1   -- comment this line if you want find indexes identical to clustered indexes as well 
SELECT 
          SCHEMA_NAME(i1.schema_id)schema_name, 
          i1.type_desc, 
          OBJECT_NAME(i1.object_id)object_name, 
          i1.index_name, 
          i1.* 
FROM cte i1 
INNERJOIN(SELECTschema_id,type_desc,object_id,index_columns 
            FROM cte 
            GROUPBYschema_id,type_desc,object_id,index_columns 
            HAVINGCOUNT(*)> 1) i2 
                      ON i1.schema_id= i2.schema_id 
           AND i1.type_desc= i2.type_desc 
           AND i1.object_id= i2.object_id 
           AND i1.index_columns= i2.index_columns 
ORDERBYschema_name, i1.type_desc,object_name, i1.index_name

 


 

Original post (opens in new tab)

Rate

5 (2)

Share

Share

Rate

5 (2)