Indicizzazione dei vincoli FOREIGN KEY

— Drop when exist
IF OBJECT_ID(‘usp_create_index_on_foreign_key’) IS NOT NULL
  DROP PROCEDURE dbo.usp_create_index_on_foreign_key
go

— Create procedure
CREATE PROCEDURE dbo.usp_create_index_on_foreign_key
(@mode tinyint,
 @tableschema nvarchar(128),
 @tablename sysname,
 @enforce_existing_index tinyint)
AS BEGIN

  /*
    Descrizione:
      Determina/genera un indice (nonclustered)
      per ogni FOREIGN KEY CONSTRAINT rilevato
      sulla tabella specificata o sull’intero
      database di riferimento

    Parametri:
      @mode = Modalità di utilizzo,
              Può assumere i valori: 1= Exec Mode, 0= Print Mode

      @tableschema = Schema di riferimento

      @tablename = Tabella di riferimento
                
      @enforce_existing_index = Applica comunque l’indice anche se ne esiste
                                già uno che interessa la stessa
                                colonna (citata nella Foreign Key)
                                Può assumere i valori: 1= Applica comunque, 0= Non applicare
  */

  DECLARE @cmd nvarchar(500),
          @count_idx integer,
          @msg nvarchar(500)

  — Controllo parametri di input
  — @mode
  IF (@mode NOT IN (0, 1))
  BEGIN
    PRINT 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))
    RETURN
  END

  — @enforce_existing_index
  IF (@enforce_existing_index NOT IN (0, 1))
  BEGIN
    PRINT OBJECT_NAME(@@PROCID) +
          ‘: Valore di input non previsto, ‘ +
          ‘@enforce_existing_index può assumere i seguenti valori: ‘ +
          ‘1= Applica comunque l”indice, 0= Non applicare l”indice ‘ +
          ‘se già esistente per la colonna interessata.’
          –‘Il valore di @enforce_existing_index passato è: ‘ +
          –LTRIM(STR(@enforce_existing_index))
    RETURN
  END

  IF (LTRIM(RTRIM(@tablename)) = ”)
    SET @tablename = NULL
  ELSE
    SET @tablename = LTRIM(RTRIM(@tablename))

  IF (LTRIM(RTRIM(@tableschema)) = ”)
    SET @tableschema = NULL
  ELSE
    SET @tableschema = LTRIM(RTRIM(@tableschema))
  
  DECLARE cur CURSOR FOR
    SELECT
      ‘CREATE NONCLUSTERED INDEX ‘ +
      ‘[IDX__’ + LTRIM(RTRIM(ccu.table_name)) + ‘_’ +
                 LTRIM(RTRIM(ccu.column_name)) + ‘] ‘ +
      ‘ON ‘ + LTRIM(RTRIM(ccu.table_schema)) + ‘.’ +
              LTRIM(RTRIM(ccu.table_name)) + ‘(‘ +
              LTRIM(RTRIM(ccu.column_name)) + ‘)’ AS cmd,

   (SELECT
         count(cu.column_id)
       FROM
         sys.indexes i
       JOIN
         sys.objects t on t.object_id=i.object_id
       JOIN
         sys.index_columns c on c.index_id=i.index_id
       JOIN
         sys.columns cu on cu.column_id=c.column_id
       WHERE
         (t.object_id=OBJECT_ID( ISNULL(@tableschema, LTRIM(RTRIM(ccu.table_schema))) + ‘.’ +
                                 ISNULL(@tablename, LTRIM(RTRIM(ccu.table_name))) )) AND
         (i.is_primary_key <> 1) AND
         (c.object_id=t.object_id) AND
         (cu.object_id=t.object_id) AND
         (cu.name=ccu.column_name)
      ) AS exists_index_on_column

    FROM
      INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    JOIN
      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON RTRIM(rc.constraint_name)=RTRIM(ccu.constraint_name)
    WHERE
      (ISNULL(@tablename, LTRIM(RTRIM(ccu.table_name))) = (LTRIM(RTRIM(ccu.table_name)))) AND
      (ISNULL(@tableschema, LTRIM(RTRIM(ccu.table_schema))) = (LTRIM(RTRIM(ccu.table_schema)))) AND
      NOT EXISTS (SELECT
                    i.object_id
                  FROM
                    sys.indexes i
                  WHERE
                    LTRIM(RTRIM(i.name))= ‘IDX__’ + LTRIM(RTRIM(ccu.table_name)) + ‘_’ +
                                                    LTRIM(RTRIM(ccu.column_name)))   

  — Open cursore cur
  OPEN cur

  — Fetch fuori ciclo
  FETCH NEXT FROM cur
    INTO @cmd, @count_idx

  IF (@mode = 1)
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Generazione indici…’
  ELSE
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Scrip indici…’

  PRINT (@msg)
  PRINT (”)

  IF (@@FETCH_STATUS <> 0)
  BEGIN
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Non sono stati rilevati indici da generare’

    IF (@tablename IS NULL) AND (@tableschema IS NULL)
      SET @msg = @msg + ‘ sul database corrente.’

    ELSE IF (@tablename IS NOT NULL) AND (@tableschema IS NOT NULL)
      SET @msg = @msg + ‘ sulla tabella ‘ + @tableschema + ‘.’ + @tablename

    ELSE IF (@tablename IS NULL) AND (@tableschema IS NOT NULL)
      SET @msg = @msg + ‘ sullo schema ‘ + @tableschema

    ELSE IF (@tablename IS NOT NULL) AND (@tableschema IS NULL)
      SET @msg = @msg + ‘ sulla tabella ‘ + @tablename

    PRINT (@msg)
  END

  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    IF (((@count_idx = 0) OR (@enforce_existing_index = 1)))
    BEGIN
      –PRINT STR(@count_idx)
      –PRINT STR(@enforce_existing_index)
      IF (@mode = 1) 
      BEGIN
        EXEC (@cmd)
        IF (@@ERROR = 0)
        BEGIN
          SET @msg = ‘Esecuzione di: ‘ + @cmd + ‘ completata correttamente.’
          PRINT (@msg)
        END
      END
      ELSE
        PRINT (@cmd)

      PRINT (”)
    END

    FETCH NEXT FROM cur
      INTO @cmd, @count_idx
  END

  — Close e deallocate cur
  CLOSE cur
  DEALLOCATE cur

  IF (@mode = 1)
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Generazione indici completata.’
  ELSE
    SET @msg = OBJECT_NAME(@@PROCID) + ‘ : Scrip indici completata.’

  PRINT (”)
  PRINT (@msg)
END

USE AdventureWorks;
go

EXEC dbo.usp_create_index_on_foreign_key 0, N’HumanResources’, N’Employee’, 0;

usp_create_index_on_foreign_key : Scrip indici…

CREATE NONCLUSTERED INDEX [IDX__Employee_ContactID] ON HumanResources.Employee(ContactID)

usp_create_index_on_foreign_key : Scrip indici completata.