Indici doppi: un sovraccarico inutile

use [AdventureWorks] go

if object_id(‘usp_drop_double_more_index’) is not null
  drop procedure dbo.usp_drop_double_more_index
go

create procedure dbo.usp_drop_double_more_index
(
  @mode tinyint,
  @object_name varchar(4000),
  @table_type varchar(10),
  @index_name_prefix_order varchar(8),
  @debug tinyint
)
as
begin
  /*
    Descrizione:
      Individua ed elimina i casi di doppia (tripla, ecc..)
      indicizzazione sui medesimi attributi

    Parametri:
      – @mode
          Modalità di utilizzo
          Può assumere i valori: 1= Exec Mode, 0= Print Mode.
          In exec-mode esegue la cancellazione degli indici
          doppi, in print-mode stampa i comandi T-SQL

      – @object_name
          Nome di una tabella o lista di nomi di tabelle
          nel formato <schema_name>.<table_name> separati da “,”
          per le quali di desidera verificare
          ed eventualmente cancellare gli indici doppi
          (esempio <schema_name_1>.<table_name_1>, <schema_name_2>.<table_name_2>, …,

          <schema_name_N>.<table_name_N>

      – @table_type
          Tipo di tabella
          Può assumere: “VIEW” o “BASE TABLE”

      – @index_name_prefix_order
          Determina, attraverso ordinamento, quale indice
          mantenere tra quelli doppi. Qualora si desideri
          mantenere un particolare indice o una particolare
          famiglia di indici il cui nome inizia ad esempio
          con “IX_”, sarà sufficiente specificare il
          prefisso desiderato in questo parametro

      – @debug
          Modalità di debug
          Può assumere i valori: 1= Debug ON, 0= Debug OFF
  */

  declare
    @msg varchar(256),
    @cmd varchar(max),
    @index_name_prefix_len int

  — Controllo parametri di input
  — @mode
  if (@mode not in (0, 1))
  begin
    set @msg = object_name(@@procid) +
              ‘ : Modalità di esecuzione non supportata, ‘ +
              ‘@mode può assumere i seguenti valori: ‘ +
              ‘1= Modalità Exec, 0= Modalità Print. ‘ +
              ‘Il valore di @mode passato è: ‘ + ltrim(str(@mode))
    raiserror(@msg, 16, 1)
    return
  end

  — @debug
  if (@debug not in (0, 1))
  begin
    set @msg = object_name(@@procid) +
              ‘ : Modalità di debug non supportata, ‘ +
              ‘@debug può assumere i seguenti valori: ‘ +
              ‘1= Debug ON, 0= Debug OFF. ‘ +
              ‘Il valore di @debug passato è: ‘ + ltrim(str(@debug))
    raiserror(@msg, 16, 1)
    return
  end

  set nocount on

  — Setup tabelle
  — tmphelpindex
  if object_id(‘tmphelpindex’) is not null
    drop table tmphelpindex

  create table tmphelpindex
  (id int identity(1, 1) not null,
   index_name sysname,
   index_description varchar(210),
   index_keys varchar(4000))

  — tmpindexes
  if object_id(‘tmpindexes’) is not null
    drop table tmpindexes

  create table tmpindexes
  (id int identity(1, 1) not null,
   table_schema varchar(128),
   table_name sysname,
   index_name sysname,
   index_description varchar(210),
   index_keys varchar(4000))

  — tmpindexes1
  if object_id(‘tmpindexes1’) is not null
    drop table tmpindexes1

  create table tmpindexes1
    (id int identity(1, 1) not null,
     table_schema varchar(128),
     table_name sysname,
     index_name sysname,
     index_description varchar(210),
     index_keys varchar(4000))

  set @cmd = ”
  set @index_name_prefix_len = len(ltrim(rtrim(@index_name_prefix_order)))

  if (@debug = 1)
  begin
    print (‘@index_name_prefix_order= “‘ + ltrim(rtrim(@index_name_prefix_order)) + ‘”‘)
    print (‘@index_name_prefix_len= ‘ + ltrim(str(@index_name_prefix_len)))
  end

  select
    @cmd = @cmd + ‘insert into tmphelpindex ‘ +
                  ‘exec sp_helpindex ”’ + i.table_schema + ‘.’ + i.table_name + ”’; ‘ +

                  ‘delete ‘ +
                    ‘t ‘ +
                  ‘from ‘ +
                    ‘tmphelpindex t ‘ +
                  ‘join ‘ +

                  ‘(select ‘ +
                     ‘min(t1.id) as id ‘ +
                   ‘from ‘ +
                     ‘tmphelpindex t1 ‘ +
                   ‘where ‘ +
                     ‘exists (select ‘ +
                               ‘id ‘ +
                             ‘from ‘ +
                               ‘tmphelpindex t2 ‘ +
                             ‘where ‘ +
                               ‘(t1.index_keys=t2.index_keys) and ‘ +
                               ‘(t2.index_description like ”clustered%”)) and ‘ +
                     ‘(t1.index_description like ”nonclustered%”)) tx on tx.id=t.id; ‘ +

                  ‘insert into tmpindexes ‘ +
                  ‘(table_schema, ‘ +
                   ‘table_name, ‘ +
                   ‘index_name, ‘ +
                   ‘index_description, ‘ +
                   ‘index_keys) ‘ +
                  ‘select ‘ +
                    ”” + i.table_schema + ”’,’ +
                    ”” + i.table_name + ”’, ‘ +
                    ‘index_name, ‘ +
                    ‘index_description, ‘ +
                    ‘index_keys ‘ +
                  ‘from ‘ +
                    ‘tmphelpindex; ‘ +

                  ‘delete from tmphelpindex; ‘
  from
    information_schema.tables as i
  where
    ((charindex((‘,’ + i.table_schema + ‘.’ + i.table_name + ‘,’),
                (‘,’ + replace(@object_name, ‘ ‘, ”) + ‘,’)) > 0) OR (@object_name=”)) and
    (i.table_type=@table_type)
  order by
    i.table_schema,
    i.table_name

  if (@debug = 1)
    print(@cmd)
  exec(@cmd)

  if (ltrim(rtrim(@index_name_prefix_order)) <> ”)
  begin
    insert into
      tmpindexes1
      (index_name,
       index_description,
       index_keys,
       table_schema,
       table_name)
    select
      index_name,
      index_description,
      index_keys,
      table_schema,
      table_name
    from
      tmpindexes
    where
      (left(index_name, @index_name_prefix_len) <> @index_name_prefix_order)

    insert into
      tmpindexes1
      (index_name,
       index_description,
       index_keys,
       table_schema,
       table_name)
    select
      index_name,
      index_description,
      index_keys,
      table_schema,
      table_name
    from
      tmpindexes
    where
      (left(index_name, @index_name_prefix_len) = @index_name_prefix_order)
  end
  else
    insert into
      tmpindexes1
      (index_name,
       index_description,
       index_keys,
       table_schema,
       table_name)
    select
      index_name,
      index_description,
      index_keys,
      table_schema,
      table_name
    from
      tmpindexes

  — Select double index on the same columns
  set @cmd = ”

  select
    @cmd = @cmd +
           ‘drop index [‘ + t.table_schema + ‘].[‘ + t.table_name + ‘].[‘ + t.index_name + ‘]; ‘ + char(13) + char(10)
  from
    tmpindexes1 t
  join
    (select
       max(id) as max_id,
       count(index_keys) as index_count,
       table_schema,
       table_name,
       index_keys,
       index_description    
     from
       tmpindexes1
     group by
       table_schema,
       table_name,
       index_keys,
       index_description
     having
       (count(index_keys) > 1)

    ) t1 on (t.table_schema = t1.table_schema) and
            (t.table_name = t1.table_name) and
            (t.index_keys = t1.index_keys) and
            (t.index_description = t1.index_description) and
            (t.id < t1.max_id)
     order by
       t.index_name

  if (@cmd = ”)
  begin
    print (”)
    print (‘+-+-+-+-+-+-+-+-+-+-+-+-+’)
    print (object_name(@@procid) + ‘: No double or more index found!’)
  end
  else begin
    print (”)
    print (‘+-+-+-+-+-+-+-+-+-+-+-+-+’)
    if (@mode = 0)
      print (object_name(@@procid) + ‘: Scrip drop index…’)
    else
      print (object_name(@@procid) + ‘: Start drop index…’)
    print (”)

    if (@mode = 0)
      print (@cmd)
    else begin
      exec (@cmd)

      if (@debug = 1)
        print (@cmd)

      print (”)
      if (@@error = 0)
        print (‘Commands: ‘ +
               char(13) + char(10) +
               @cmd + ‘ completed successfully.’)
      else
        print (‘Commands: ‘ +
               char(13) + char(10) +
               @cmd + ‘ ended with errors.’)
      print (”)
    end
  end

  print (”)
  print (object_name(@@procid) + ‘: …Execution end.’)
  print (‘+-+-+-+-+-+-+-+-+-+-+-+-+’)

  — Pulizia tabelle
  — tmphelpindex
  if object_id(‘tmphelpindex’) is not null
    drop table tmphelpindex

  — tmpindexes
  if object_id(‘tmpindexes’) is not null
    drop table tmpindexes

  — tmpindexes1
  if object_id(‘tmpindexes1’) is not null
    drop table tmpindexes1

  set nocount off
end

create nonclustered index
  [IX__Person_Contact_FirstName] on [Person].[Contact](FirstName);
go

create nonclustered index
  [IX__Person_Contact_FirstName_1] on [Person].[Contact](FirstName);
go

create nonclustered index
  [IX__Person_Contact_FirstName_2] on [Person].[Contact](FirstName)

EXEC dbo.usp_drop_double_more_index
  @mode=0,
  @object_name=’Person.Contact’,
  @table_type=’base table’,
  @index_name_prefix_order = ”,
  @debug=1

+-+-+-+-+-+-+-+-+-+-+-+-+
usp_drop_double_more_index: Scrip drop index…

drop index [Person].[Contact].[IX__Person_Contact_FirstName];
drop index [Person].[Contact].[IX__Person_Contact_FirstName_1];

usp_drop_double_more_index: …Execution end.
+-+-+-+-+-+-+-+-+-+-+-+-+

EXEC dbo.usp_drop_double_more_index
  @mode=1,
  @object_name=”,
  @table_type=’base table’,
  @index_name_prefix_order = ”,
  @debug=1

+-+-+-+-+-+-+-+-+-+-+-+-+
usp_drop_double_more_index: Start drop index…

drop index [Person].[Contact].[IX__Person_Contact_FirstName];
drop index [Person].[Contact].[IX__Person_Contact_FirstName_1];

Commands:
drop index [Person].[Contact].[IX__Person_Contact_FirstName];
drop index [Person].[Contact].[IX__Person_Contact_FirstName_1];
 completed successfully.

usp_drop_double_more_index: …Execution end.
+-+-+-+-+-+-+-+-+-+-+-+-+