Buscar este blog

jueves, 27 de octubre de 2016

Scriptear Todos los Indices de Una Base de Datos (con un solo script)

Amigos,

   Cuantas veces nos encontramos ante una migración con la necesidad de contar con la ayuda de un script que nos permita scriptear todos los Indices de todas las tablas de una base de datos?
   Estoy seguro que muchas veces se han efrentado a esta situación y, por cierto, no parece la situación ideal "pararse" sobre cada tabla y con el botón derecho scriptear cada uno de los idx...
   Les dejo entonces un script que hace esto por ustedes, solo lo ejeutan y el resultado en formato texto será el script con el cual podrán regenerar uno a uno todos los índices de todas las tablas de una base de datos.
   Aprovecho para saludar al amigo Agapito Buenaventura quien me ha escrito preguntando por este tema desde la querida Bolivia.

  ---------------------------------------------------------------------------
-- Autor Gustavo Herrera                                                
-- Detalle: Este código scriptea todos los indices correspondientes a una BD 
-----------------------------------------------------------------------------

DECLARE cIX CURSOR FOR
   SELECT
   OBJECT_NAME(SI.Object_ID),
   SI.Object_ID,
   SI.Name,
   SI.Index_ID,
   F.NAME
   FROM Sys.Indexes SI
   LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
   ON
    SI.Name = TC.CONSTRAINT_NAME AND
    OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
   LEFT JOIN sys.filegroups f -- para saber el filegroup
   ON SI.data_space_id = f.data_space_id
   WHERE --TC.CONSTRAINT_NAME IS NULL --AND
         OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
          ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

   DECLARE
   @IxTable SYSNAME,
   @IxTableID INT,
   @IxName SYSNAME,
   @IxID INT,
   @PKSQL varchar (50),
   @FILEGROUP VARCHAR (50)

   -- Loopea a través de todos los idx
   OPEN cIX
   FETCH NEXT FROM cIX INTO
   @IxTable, @IxTableID, @IxName, @IxID, @FILEGROUP
  
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
       DECLARE @IXSQL NVARCHAR(4000) SET @PKSQL = ''
       SET @IXSQL = 'CREATE '

       -- Chequea si el indice es unique
       IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
        SET @IXSQL = @IXSQL + 'UNIQUE '
       -- Chequea si el idx es clustered
       IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
        SET @IXSQL = @IXSQL + 'CLUSTERED '
        SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

         -- Obtiene todas las columnas que contienen índices
         DECLARE cIxColumn CURSOR FOR
              SELECT SC.Name
              FROM Sys.Index_Columns IC
                   JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
              WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
              ORDER BY IC.Index_Column_ID

            DECLARE
            @IxColumn SYSNAME,
            @IxFirstColumn BIT
            SET @IxFirstColumn = 1

            -- Loopea a través de todas las columnas que forman un idx y les agrega un CREATE
            OPEN cIxColumn
            FETCH NEXT FROM cIxColumn INTO @IxColumn
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                IF (@IxFirstColumn = 1)
                    SET @IxFirstColumn = 0
                ELSE
                    SET @IXSQL = @IXSQL + ', '
             SET @IXSQL = @IXSQL + @IxColumn

                  FETCH NEXT FROM cIxColumn INTO @IxColumn
            END
            CLOSE cIxColumn
            DEALLOCATE cIxColumn

            SET @IXSQL = @IXSQL + ') '+'ON '+@FILEGROUP
            
            
           
          -- Imprime CREATE statement para los índices
   PRINT @IXSQL

   FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @FILEGROUP
END

CLOSE cIX
DEALLOCATE cIX
 

1 comentario: