Outro script para gerar todos os índices no SQL SERVER 2005/2008

Como o outro script está um pouco falho, decidi pesquisar um outro script para gerar os índices.

Aqui está:

DECLARE @PKSQL NVARCHAR(4000)

-- Get all existing indexes, but NOT the primary keys

DECLARE cIX CURSOR FOR

   SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID

      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

      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

DECLARE @IxTableID INT

DECLARE @IxName SYSNAME

DECLARE @IxID INT

-- Loop through all indexes

OPEN cIX

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

WHILE (@@FETCH_STATUS = 0)

BEGIN

   DECLARE @IXSQL NVARCHAR(4000) SET @PKSQL = ''

   SET @IXSQL = 'CREATE '

   -- Check if the index is unique

   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)

      SET @IXSQL = @IXSQL + 'UNIQUE '

   -- Check if the index is clustered

   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)

      SET @IXSQL = @IXSQL + 'CLUSTERED '

   SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

   -- Get all columns of the index

   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

   DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

   -- Loop throug all columns of the index and append them to the CREATE statement

   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 + ')'

   -- Print out the CREATE statement for the index

   PRINT @IXSQL

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

END

CLOSE cIX

DEALLOCATE cIX

Original: http://www.sqlservercentral.com/scripts/Miscellaneous/31893/

Recentemente, verifiquei que alguns visitantes comentaram sobre a falta dos INCLUDES nos scripts. Atualmente, estou usando o script abaixo para gerar os scripts de maneira mais completa:

DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @ColumnName VARCHAR(100)
DECLARE @is_unique VARCHAR(100)
DECLARE @IndexTypeDesc VARCHAR(100)
DECLARE @FileGroupName VARCHAR(100)
DECLARE @is_disabled VARCHAR(100)
DECLARE @IndexOptions VARCHAR(max)
DECLARE @IndexColumnId INT
DECLARE @IsDescendingKey INT
DECLARE @IsIncludedColumn INT
DECLARE @TSQLScripCreationIndex VARCHAR(max)
DECLARE @TSQLScripDisableIndex VARCHAR(max)
DECLARE cursorindex CURSOR FOR
  SELECT Schema_name(t.schema_id)             [schema_name],
         t.NAME,
         ix.NAME,
         CASE
           WHEN ix.is_unique = 1 THEN 'UNIQUE '
           ELSE ''
         END,
         ix.type_desc,
         CASE WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, ' ELSE
         'PAD_INDEX = OFF, '
                                        END +
         CASE WHEN ix.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE
         'ALLOW_PAGE_LOCKS = OFF, ' END + CASE WHEN ix.allow_row_locks=1 THEN
         'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END + CASE WHEN
         Indexproperty(t.object_id, ix.NAME, 'IsStatistics') = 1 THEN
         'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, '
         END
                                        + CASE
         WHEN ix.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE
         'IGNORE_DUP_KEY = OFF, ' END
         + 'SORT_IN_TEMPDB = OFF, FILLFACTOR ='
         + Cast(ix.fill_factor AS VARCHAR(3)) AS IndexOptions,
         ix.is_disabled,
         Filegroup_name(ix.data_space_id)     FileGroupName
  FROM   sys.tables t
         INNER JOIN sys.indexes ix
                 ON t.object_id = ix.object_id
  WHERE  ix.type > 0
         AND ix.is_primary_key = 0
         AND ix.is_unique_constraint = 0
         --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
         AND t.is_ms_shipped = 0
         AND t.NAME <> 'sysdiagrams'
  ORDER  BY Schema_name(t.schema_id),
            t.NAME,
            ix.NAME

OPEN cursorindex

FETCH next FROM cursorindex INTO @SchemaName, @TableName, @IndexName, @is_unique
, @IndexTypeDesc, @IndexOptions, @is_disabled, @FileGroupName

WHILE ( @@fetch_status = 0 )
  BEGIN
      DECLARE @IndexColumns VARCHAR(max)
      DECLARE @IncludedColumns VARCHAR(max)

      SET @IndexColumns=''
      SET @IncludedColumns=''

      DECLARE cursorindexcolumn CURSOR FOR
        SELECT col.NAME,
               ixc.is_descending_key,
               ixc.is_included_column
        FROM   sys.tables tb
               INNER JOIN sys.indexes ix
                       ON tb.object_id = ix.object_id
               INNER JOIN sys.index_columns ixc
                       ON ix.object_id = ixc.object_id
                          AND ix.index_id = ixc.index_id
               INNER JOIN sys.columns col
                       ON ixc.object_id = col.object_id
                          AND ixc.column_id = col.column_id
        WHERE  ix.type > 0
               AND ( ix.is_primary_key = 0
                      OR ix.is_unique_constraint = 0 )
               AND Schema_name(tb.schema_id) = @SchemaName
               AND tb.NAME = @TableName
               AND ix.NAME = @IndexName
        ORDER  BY ixc.index_column_id

      OPEN cursorindexcolumn

      FETCH next FROM cursorindexcolumn INTO @ColumnName, @IsDescendingKey,
      @IsIncludedColumn

      WHILE ( @@fetch_status = 0 )
        BEGIN
            IF @IsIncludedColumn = 0
              SET @IndexColumns=@IndexColumns + @ColumnName + CASE WHEN
                                @IsDescendingKey
                                =1
                                                         THEN ' DESC, ' ELSE
                                                         ' ASC, ' END
            ELSE
              SET @IncludedColumns=@IncludedColumns + @ColumnName + ', '

            FETCH next FROM cursorindexcolumn INTO @ColumnName, @IsDescendingKey
            ,
            @IsIncludedColumn
        END

      CLOSE cursorindexcolumn

      DEALLOCATE cursorindexcolumn

      SET @IndexColumns = Substring(@IndexColumns, 1, Len(@IndexColumns) - 1)
      SET @IncludedColumns = CASE
                               WHEN Len(@IncludedColumns) > 0 THEN
                               Substring(@IncludedColumns, 1,
                               Len(@IncludedColumns) - 1)
                               ELSE ''
                             END
      --  print @IndexColumns
      --  print @IncludedColumns
      SET @TSQLScripCreationIndex =''
      SET @TSQLScripDisableIndex =''
      SET @TSQLScripCreationIndex='CREATE ' + @is_unique + @IndexTypeDesc
                                  + ' INDEX ' + Quotename(@IndexName) + ' ON '
                                  + Quotename(@SchemaName) + '.'
                                  + Quotename(@TableName) + '(' + @IndexColumns
                                  + ') ' + CASE WHEN Len(@IncludedColumns)>0
                                  THEN
                                  Char
                                  (13) +'INCLUDE (' + @IncludedColumns+ ')' ELSE
                                  ''
                                  END + Char(13) + 'WITH (' + @IndexOptions
                                  + ') ON ' + Quotename(@FileGroupName) + ';'

      IF @is_disabled = 1
        SET @TSQLScripDisableIndex= Char(13) + 'ALTER INDEX '
                                    + Quotename(@IndexName) + ' ON '
                                    + Quotename(@SchemaName) + '.'
                                    + Quotename(@TableName) + ' DISABLE;' + Char
                                    (
                                    13)

      PRINT @TSQLScripCreationIndex

      PRINT @TSQLScripDisableIndex

      FETCH next FROM cursorindex INTO @SchemaName, @TableName, @IndexName,
      @is_unique, @IndexTypeDesc, @IndexOptions, @is_disabled, @FileGroupName
  END

CLOSE cursorindex

DEALLOCATE cursorindex 

Fonte: https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/

Abs!