Sunday 1 August 2010

Automatically Script Non Clustered Indexes

As part of a larger backup project I wanted to automatically script my non clustered indexes to a file and have set about writing a procedure to do this. Here are the steps to follow if you want to achieve the same.

1) Enable OLE automation

2) Create utils.usp_OLEwritefile (sp that Writes to a text file from TSQL)

3) Create utils.usp_GenerateIndexesScript as follows -

/* 
Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. )

Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com

 1) Changed Schema of routine to Utils
 2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript
 3) Added Schemas to script
 4) Reformatted for clarity
 5) Compression Option added with compression type
 6) Existing Index detection script changed to use sys.indexes and sys.objects
 7) Fix for Included Column Ordering
 
--  Usage: 

EXEC utils.usp_GenerateIndexesScript 
  @IncludeFileGroup   = 1
    ,@IncludeDrop    = 1
    ,@IncludeFillFactor   = 1
    ,@destinationparameter  = '\\SERVER-01\sqlbackup$\Indexes\myindexes.sql'
    ,@IncludeCompression  = 1    
    ,@compressionType   = 'PAGE'
*/ 
CREATE PROCEDURE [utils].[usp_GenerateIndexesScript]
(
     @IncludeFileGroup  bit = 1
    ,@IncludeDrop   bit = 1
    ,@IncludeFillFactor  bit = 1
    ,@destinationparameter  NVARCHAR(1000) = NULL
    ,@IncludeCompression bit = 1
    ,@compressionType  VARCHAR(4) = 'PAGE'
 
)
AS

BEGIN
    -- Get all existing indexes, but NOT the primary keys
    DECLARE Indexes_cursor CURSOR
        FOR SELECT 
     SC.Name   AS SchemaName
     , SO.Name  AS TableName
                    , SI.Object_Id  AS TableId
     , SI.[Name]     AS IndexName
     , SI.Index_ID   AS IndexId
     , FG.[Name]     AS FileGroupName
     , CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
              FROM sys.indexes SI
              LEFT JOIN sys.filegroups FG
                     ON SI.data_space_id = FG.data_space_id
              INNER JOIN sys.objects SO
     ON SI.object_id = SO.object_id
     INNER JOIN sys.schemas SC
     ON SC.schema_id = SO.schema_id
             WHERE ObjectProperty(SI.Object_Id, 'IsUserTable') = 1
               AND SI.[Name] IS NOT NULL
               AND SI.is_primary_key = 0
               AND SI.is_unique_constraint = 0
               AND IndexProperty(SI.Object_Id, SI.[Name], 'IsStatistics') = 0
               AND FG.[Name]  IS NOT NULL
             ORDER BY SC.Name , SO.Name ,SI.[Name]    

    DECLARE @SchemaName  sysname
    DECLARE @TableName  sysname
    DECLARE @TableId  int
    DECLARE @IndexName  sysname
    DECLARE @FileGroupName sysname
    DECLARE @IndexId  int
    DECLARE @FillFactor  int

    DECLARE @NewLine nvarchar(4000)     
    SET @NewLine = CHAR(13) + CHAR(10)
    
    DECLARE @Tab nvarchar(4000)     
    SET @Tab = Space(4)

 DECLARE @SQLOutput nvarchar(max) 
 SET @SQLOutput = ''
 
    -- Loop through all indexes
    OPEN Indexes_cursor

    FETCH NEXT
     FROM Indexes_cursor
     INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

    WHILE (@@Fetch_Status = 0)
        BEGIN

            DECLARE @sIndexDesc nvarchar(4000)
            DECLARE @sCreateSql nvarchar(4000)
            DECLARE @sDropSql  nvarchar(4000)

            SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @SchemaName + '.' + @TableName
            SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
                          + '            FROM sys.indexes si' + @NewLine
                          + '            INNER JOIN sys.objects so' + @NewLine
                          + '                   ON so.object_id = si.object_id' + @NewLine
                          + '           WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
                          + '             AND so.[Name] = N''' + @TableName + ''')  -- Table Name' + @NewLine
                          + 'BEGIN' + @NewLine
                          + '    DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
                          + 'END' + @NewLine

            SET @sCreateSql = 'CREATE '

            -- Check if the index is unique
            IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'UNIQUE '
                END
            --END IF
            -- Check if the index is clustered
            IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'CLUSTERED '
                END
            --END IF

            SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

            -- Get all columns of the index
            DECLARE IndexColumns_cursor CURSOR
                FOR SELECT SC.[Name],
                           IC.[is_included_column],
                           IC.is_descending_key
                      FROM sys.index_columns IC
                     INNER JOIN sys.columns SC
                             ON IC.Object_Id = SC.Object_Id
                            AND IC.Column_ID = SC.Column_ID
                     WHERE IC.Object_Id = @TableId
                       AND Index_ID = @IndexId
                     ORDER BY IC.[is_included_column],
         IC.key_ordinal

            DECLARE @IxColumn   sysname
            DECLARE @IxIncl     bit
            DECLARE @Desc     bit
            DECLARE @IxIsIncl     bit     SET @IxIsIncl = 0
            DECLARE @IxFirstColumn  bit     SET @IxFirstColumn = 1

            -- Loop through all columns of the index and append them to the CREATE statement
            OPEN IndexColumns_cursor
            FETCH NEXT
             FROM IndexColumns_cursor
             INTO @IxColumn, @IxIncl, @Desc

            WHILE (@@Fetch_Status = 0)
                BEGIN
                    IF (@IxFirstColumn = 1)
                        BEGIN
                            SET @IxFirstColumn = 0
                        END
                    ELSE
                        BEGIN
                            --check to see if it's an included column
                            IF (@IxIsIncl = 0) AND (@IxIncl = 1)
                                BEGIN
                                    SET @IxIsIncl = 1
                                    SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ',' + @NewLine
                                END
                            --END IF
                        END
                    --END IF

                    SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
                    -- check if ASC or DESC
                    IF @IxIsIncl = 0
                        BEGIN
                            IF @Desc = 1
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' DESC'
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' ASC'
                                END
                            --END IF
                        END
                    --END IF
                    FETCH NEXT
                     FROM IndexColumns_cursor
                     INTO @IxColumn, @IxIncl, @Desc
                END
            --END WHILE
            CLOSE IndexColumns_cursor
            DEALLOCATE IndexColumns_cursor

            SET @sCreateSql = @sCreateSql + @NewLine + ') '

            IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (' 
                  END

            IF @IncludeFillFactor = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql  + 'FillFactor = ' + Cast(@FillFactor as varchar(13)) 
                END
     
                IF @IncludeCompression = 1
                BEGIN
        IF @IncludeFillFactor = 1
       BEGIN
        SET @sCreateSql = @sCreateSql + ','
       END
                    SET @sCreateSql = @sCreateSql  + 'DATA_COMPRESSION = ' + @compressionType
                END
                
                 IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql  + ')' + @NewLine
                END
                            
            --END IF

            IF @IncludeFileGroup = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                END
            ELSE
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine
                END
            --END IF

            PRINT '-- **********************************************************************'
            PRINT @sIndexDesc
            PRINT '-- **********************************************************************'


  SET @SQLOutput = @SQLOutput + '-- **********************************************************************' + @NewLine
        SET @SQLOutput = @SQLOutput +  @sIndexDesc + @NewLine
        SET @SQLOutput = @SQLOutput +  '-- **********************************************************************' + @NewLine
  SET @SQLOutput = @SQLOutput + @NewLine


            IF @IncludeDrop = 1
                BEGIN
                    PRINT @sDropSql
                    PRINT 'GO'
                    
                   SET @SQLOutput = @SQLOutput + @sDropSql + @NewLine
                   SET @SQLOutput = @SQLOutput + 'GO' + @NewLine 
                    
                END
            --END IF

            PRINT @sCreateSql
            PRINT 'GO' + @NewLine  + @NewLine
           
            SET @SQLOutput = @SQLOutput + @sCreateSql + @NewLine
            SET @SQLOutput = @SQLOutput + 'GO' + @NewLine  


            FETCH NEXT
             FROM Indexes_cursor
             INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
        END

    --END WHILE
    CLOSE Indexes_cursor
    DEALLOCATE Indexes_cursor
    
    -- @SQLOutput contains the output to place in a file
    

IF LEN(@destinationparameter) > 0
 BEGIN

 EXEC utils.usp_OLEwritefile  @FileName = @destinationparameter
        , @TextData = @SQLOutput
        , @FileAction = 'CREATENEW'
 END    
    
    --PRINT @SQLOutput
END


Run the procedure to script the indexes as follows -
The compression and destination file parameters are optional!

EXEC utils.usp_GenerateIndexesScript 
     @IncludeFileGroup   = 1
    ,@IncludeDrop   = 1
    ,@IncludeFillFactor   = 1
    ,@destinationparameter  = 'c:\MyNonClusteredIndexes.sql'
    ,@IncludeCompression  = 1    
    ,@compressionType   = 'PAGE'

No comments: