Tuesday, June 22, 2010

Generate Indexes Script - usp_GenerateIndexesScript

This is a procedure that generates scripts for your indexes.
Yes, you can do that by a few right click actions in Management Studio, but what if you need to automate it?
Included in this version-
  • File output - needs OLE
  • File Groups
  • Drop Statements
  • Fill Factor
  • Compression
  • Table Schemas

    IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA 
    WHERE SCHEMA_NAME = 'utils'
    AND SCHEMA_OWNER = 'dbo')
    BEGIN
        EXEC('CREATE SCHEMA utils AUTHORIZATION dbo')
    END
    GO
    
    IF NOT EXISTS(SELECT 1
                    FROM INFORMATION_SCHEMA.ROUTINES
                   WHERE ROUTINE_NAME = 'usp_GenerateIndexesScript'
                     AND ROUTINE_TYPE = N'PROCEDURE')
    BEGIN
        EXEC ('CREATE PROCEDURE [utils].[usp_GenerateIndexesScript] AS BEGIN SELECT 1 END')
    END
    GO
    
    /* 
    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
    
    --  Usage: 
    
    EXEC utils.usp_GenerateIndexesScript 
      @IncludeFileGroup    = 1
        ,@IncludeDrop      = 1
        ,@IncludeFillFactor    = 1
        ,@IncludeCompression  = 1    
        ,@destinationparameter  = '\\Server\sqlbackup$\Indexes\'
    */ 
    
    IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA 
    WHERE SCHEMA_NAME = 'utils'
    AND SCHEMA_OWNER = 'dbo')
    BEGIN
        EXEC('CREATE SCHEMA utils AUTHORIZATION dbo')
    END
    GO
    
    IF NOT EXISTS(SELECT 1
                    FROM INFORMATION_SCHEMA.ROUTINES
                   WHERE ROUTINE_NAME = 'GenerateIndexesScript'
                     AND ROUTINE_TYPE = N'PROCEDURE')
    BEGIN
        EXEC ('CREATE PROCEDURE [utils].[GenerateIndexesScript] AS BEGIN SELECT 1 END')
    END
    GO
    
    ALTER PROCEDURE utils.usp_GenerateIndexesScript
    (
         @IncludeFileGroup   bit = 1
        ,@IncludeDrop     bit = 1
        ,@IncludeFillFactor   bit = 1
        ,@IncludeCompression bit = 1
        ,@destinationparameter NVARCHAR(1000) = NULL
     
    )
    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 Object_name(SI.Object_Id), SI.Index_ID
    
        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 ' + @TableName
                SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
                              + '            FROM sysindexes si' + @NewLine
                              + '            INNER JOIN sysobjects so' + @NewLine
                              + '                   ON so.id = si.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.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 = PAGE'
                    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
      DECLARE @destinationfile varchar(500)
      DECLARE @destinationpath NVARCHAR(1000)
      DECLARE @as_at DATETIME
      DECLARE @databasename VARCHAR(100)
      DECLARE @servername VARCHAR(100)
    
      SET @servername = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(100))
      SET @databasename = CAST(DB_NAME() AS VARCHAR(100))
      SET @as_at = GETDATE()
    
      SET @destinationpath = @destinationparameter + @servername
      EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath
      SET @destinationpath = @destinationpath + N'\' + @databasename
      EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath
      SET @destinationpath = @destinationpath + N'\' + REPLACE(REPLACE(CONVERT(NVARCHAR(20),@as_at,120),' ','.'),':','')
      EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath
    
      SET @destinationfile = @destinationpath + '\' + @databasename + '_Indexes_'+ REPLACE(REPLACE(CONVERT(NVARCHAR(20),@as_at,120),' ','.'),':','') + '.sql'
    
      EXEC sql_tools.utils.usp_OLEwritefile  @FileName = @destinationfile
            , @TextData = @SQLOutput
            , @FileAction = 'CREATENEW'
    END    
        
        --PRINT @SQLOutput
    END
    
    GO
    

    You execute the procedure like this -
    EXEC sql_tools.utils.usp_GenerateIndexesScript 
    @IncludeFileGroup  = 1
        ,@IncludeDrop = 1
        ,@IncludeFillFactor = 1
        ,@IncludeCompression  = 1    
        ,@destinationparameter = '\\SERVER07\sqlbackup$\Indexes\'
        
    GO
    

    No comments: