/***********************************************************************************************************************
        Version 1.0
        19 Aug 2010
 
        Gregory Ferdinanddsen
        [email protected]
 
 
            This SP will rebuild/reorg indexes.
 
        Parameters:
            @DB = Either 'All' or the name of one DB. If 'All' all databases on the server are examined; otherwise the name of a single DB.
            @Stats = Statistical Sampling Method (Limited, Sampled, or Detailed) for determining what index will be impacted.
                --LIMITED - It is the fastest mode and scans the smallest number of pages. 
                        For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned
                --SAMPLED - It returns statistics based on a 1 percent sample of all the pages in the index or heap. 
                        If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.
                --DETAILED - It scans all pages and returns all statistics.
            @MinPageCount = Since index with few pages usually don't defrag (and a table scan is preferred), ignores small indexes
            @MaxPageCount = Maximum number of index pages to be considered. This can preclude very large indexes
            @Fill Factor = Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page 
                    during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.
            @PAD_Index = The percentage of free space that is specified by FILLFACTOR is applied to the intermediate-level pages of the index. 
                If FILLFACTOR is not specified at the same time PAD_INDEX is set to ON, the fill factor value stored in sys.indexes is used.
            @SortInTempDB = The intermediate sort results that are used to build the index are stored in tempdb. 
                If tempdb is on a different set of disks than the user database, this may reduce the time needed to create an index. 
                However, this increases the amount of disk space that is used during the index build.
            @Online = Online rebuild, for editions that support online rebuild (for editions that do not support online rebuild, this is ignored)
            @ReBuildTheshold = The threshold for deciding to rebuild v reorg (MSFT recomend's 30)
            @ReOrgThreshold = The threshold for deciding to rebuild v reorg (MSFT recomend's 5)
            @MaxFrag = The maximum amount of fragmentation to defrag (i.e. you don't want to defrag an index over 80%)
            @ChangeRecoveryModel = Set's the DB's in simple recovery mode prior to starting, reverts back to original mode on completion.
 
            NB:
            @Fill_Factor, @PAD_Index will only be applied to index that are rebuilt (Fragmentation >= @ReBuildTheshold)
 
 
            Alter Index -- http://technet.microsoft.com/en-us/library/ms188388.aspx
            sys.dm_db_index_physical_stats -- http://msdn.microsoft.com/en-us/library/ms188917.aspx
 
        examples:
            exec dbadmin..sp_Defrag_Indexes, @FillFactor = 75, @PAD_Index = 'true', @Stats = 'Detailed'
 
            exec dbadmin..sp_Defrag_Indexes
                @DB = 'changepoint',
                @FillFactor = 65,
                @PAD_Index = 'true',
                @Stats = 'Detailed',
                @ChangeRecoveryModel = 'true',
                @minpagecount = 150
***********************************************************************************************************************/
 
ALTER PROCEDURE [dbo].[sp_Defrag_Indexes]
    (
    @DB VARCHAR(256) = 'all',
    @Stats VARCHAR(8) = 'sampled',
    @MinPageCount INT = 20,
    @MaxPageCount FLOAT = 1000000000000000, --A very large default number
    @FillFactor INT = NULL,
    @PAD_Index VARCHAR(8) = 'false',
    @SortInTempDB VARCHAR(8) = 'true',
    @OnlineReq VARCHAR(8) = 'true',
    @ReBuildTheshold REAL = 30.0,
    @ReOrgThreshold REAL = 5.0,
    @MaxFrag REAL = 100.0,
    @ChangeRecoveryModel VARCHAR(8) = 'false'
    )
 
    AS
 
    DECLARE @SQLCmd AS VARCHAR (8000)
    DECLARE @SQLCmdBk AS VARCHAR(4096)
    DECLARE @SQLCmdWith AS VARCHAR(4096)
    DECLARE @SQLCmdFill VARCHAR(512)
    DECLARE @SQLCmdOnline VARCHAR(512)
    DECLARE @SQLCmdPad VARCHAR(512)
    DECLARE @SQLCmdSort VARCHAR(512)
    DECLARE @SQLCmdRecovery VARCHAR(512)
    DECLARE @exit VARCHAR(8)
    DECLARE @ErrorTxt AS VARCHAR(128)
    DECLARE @SQLEdition AS VARCHAR(64)
    DECLARE @Online AS VARCHAR(8)
    DECLARE @DBName AS VARCHAR(256)
    DECLARE @ObjectID INT
    DECLARE @IndexID INT
    DECLARE @PartitionNum AS BIGINT
    DECLARE @Frag AS FLOAT
    DECLARE @PageCount AS BIGINT
    DECLARE @PartitionCount AS BIGINT
    DECLARE @ParititionNum AS BIGINT
    DECLARE @IndexName AS VARCHAR(128)
    DECLARE @SchemaName AS VARCHAR(128)
    DECLARE @ObjectName AS VARCHAR(128)
    DECLARE @ParmDef nvarchar(512)
    DECLARE @SQLCmdID AS nvarchar(1024)
    DECLARE @RecoveryModel AS VARCHAR(16)
 
    --Verify that proper parameters were passed to SP
    IF @Stats NOT IN ('limited', 'sampled', 'detailed')
        BEGIN
            RaisError ('@Stats must be "limited", "sampled", or "detailed"', 16, 1)
            RETURN
        END
 
    IF @PAD_Index NOT IN ('true', 'false')
        BEGIN
            RaisError ('@PAD_Index must be "true" or "false"', 16, 1)
            RETURN
        END
 
    IF @SortInTempDB NOT IN ('true', 'false')
        BEGIN
            RaisError ('@SortInTempDB must be "true" or "false"', 16, 1)
            RETURN
        END
 
    IF @OnlineReq NOT IN ('true', 'false')
        BEGIN
            RaisError ('@OnlineReq must be "true" or "false"', 16, 1)
            RETURN
        END
 
    IF @FillFactor NOT BETWEEN 0 AND 100
        BEGIN
            RaisError ('@FillFactor must be between 0 and 100', 16, 1)
            RETURN
        END
 
    IF @ReBuildTheshold NOT BETWEEN 1 AND 100
        BEGIN
            RaisError ('@ReBuildTheshold must be between 1 and 100', 16, 1)
            RETURN
        END
 
    IF @ReOrgThreshold NOT BETWEEN 1 AND 100
        BEGIN
            RaisError ('@ReOrgThreshold must be between 1 and 100', 16, 1)
            RETURN
        END
 
    --There would be nothing returned if MaxFrag was less than the reorg threshold.
    IF @MaxFrag NOT BETWEEN @ReOrgThreshold AND 100
        BEGIN
            RaisError ('@MaxFrag must be between the @ReOrgThreshold value (default of 5) and 100', 16, 1)
            RETURN
        END
 
    IF @MinPageCount < 0
        BEGIN
            RaisError ('@MinPageCount must be positive', 16, 1)
            RETURN
        END
 
    IF @MaxPageCount < 10
        BEGIN
            RaisError ('@MaxPageCount must be greater than 10', 16, 1)
            RETURN
        END
 
    IF @ChangeRecoveryModel NOT IN ('true', 'false')
        BEGIN
            RaisError ('@ChangeRecoveryModel must be "true" or "false"', 16, 1)
            RETURN
        END
 
    IF @MinPageCount > @MaxPageCount
        BEGIN
            RaisError ('@MinPageCount cannot be greater than @MaxPageCount', 16, 1)
            RETURN
        END
 
    IF @DB <> 'All'
        BEGIN
            IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = @DB)
                BEGIN
                    SET @ErrorTxt = 'The supplied database (' + @DB + ') does not exist.'
                    RaisError (@ErrorTxt, 16, 1)
                    RETURN
                END
        END
 
    --You can't have rebuild be at a lower level than reorg
    IF @ReBuildTheshold < @ReOrgThreshold SET @ReOrgThreshold = @ReBuildTheshold - 0.01
 
    --Determine SQL Edition (for online rebuild -- Enterprise and Developer support online rebuild)
    SET @SQLEdition = CAST(ServerProperty('Edition') AS VARCHAR)
    SET @SQLEdition =
        CASE 
            WHEN @SQLEdition = 'Enterprise Edition' THEN 'Enterprise'
            WHEN @SQLEdition = 'Standard Edition' THEN 'Standard'
            WHEN @SQLEdition = 'Developer Edition' THEN 'Developer'
        END
    IF @SQLEdition = 'Enterprise' OR @SQLEdition = 'Developer'
        BEGIN
            SET @Online = 'true'
        END
    ELSE SET @Online = 'false'
 
    --If only one database, then go to the innser cursor (and exit that cursor before the fetch next command)
    SET @Exit = 'false'
    IF @DB <> 'All'
        BEGIN
            SET @Exit = 'true'
            SET @DBName = @DB
            GOTO ExecuteForEachDatabase
        END
 
    --Outer Cursor for DBName
    DECLARE DatabaseNames cursor
        FOR SELECT name FROM sys.databases
 
        OPEN DatabaseNames
        fetch NEXT FROM DatabaseNames INTO @DBName
 
        while @@fetch_status <> -1
            BEGIN
ExecuteForEachDatabase:
                --Delete the Temp Table
                IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '#Fragmentation' AND TYPE IN('U'))
                    BEGIN
                        DROP TABLE #Fragmentation
                    END
 
                --Determine Recovery Model
                SET @RecoveryModel = CAST(DatabasePropertyEx(@DBName, 'Recovery') AS VARCHAR(16))
                IF @RecoveryModel IN ('FULL', 'BULK_LOGGED') AND @ChangeRecoveryModel = 'true'
                    BEGIN
                        SET @SQLCmdRecovery = 'alter database ' + @DBName + ' set recovery simple with no_wait'
                        print @DBName + ' recovery model set to simple.'
                        EXEC (@SQLCmdRecovery)
                    END
 
                --Index_ID of 0 is a heap index, no need to defrag
                SELECT object_id AS ObjectID, index_id AS IndexID, partition_number AS PartitionNum, avg_fragmentation_in_percent AS Frag
                    INTO #Fragmentation
                    FROM sys.dm_db_index_physical_stats (DB_ID(@DBName), NULL, NULL , NULL, @Stats)
                    WHERE avg_fragmentation_in_percent >= @ReOrgThreshold AND avg_fragmentation_in_percent < = @MaxFrag
                        AND index_id > 0
                        AND Page_Count >= @MinPageCount AND Page_Count <= @MaxPageCount
 
                --Inner Cursor (objects)
                DECLARE CurPartitions cursor
                    FOR SELECT * FROM #Fragmentation
 
                    OPEN CurPartitions
                    fetch NEXT FROM CurPartitions INTO @ObjectID, @IndexID, @ParititionNum, @Frag
 
                    while @@fetch_status <> -1
                        BEGIN
                            SET @SQLCmdID = 'select @ObjectName = quotename(obj.name), @SchemaName = quotename(sch.name) from ' + @DBName + 
                                '.sys.objects as obj join ' + @DBName + '.sys.schemas as sch on sch.schema_id = obj.schema_id where obj.object_id = @ObjectID'
                            --select @ObjectName = quotename(obj.name), @SchemaName = quotename(sch.name)
                            --    from sys.objects as obj
                            --    join sys.schemas as sch on sch.schema_id = obj.schema_id
                            --    where obj.object_id = @ObjectID
                            SET @ParmDef = N'@ObjectID int, @ObjectName sysname output, @SchemaName sysname output'
                            EXEC sp_executesql @SQLCmdID, @ParmDef, @ObjectID= @ObjectID, @ObjectName = @ObjectName output, @SchemaName = @SchemaName output
                                
 
                            --select @IndexName = quotename(name)
                            --    from sys.indexes
                            --    where object_id = @ObjectID and index_id = @IndexID
                            SET @SQLCmdID = 'select @IndexName = quotename(name) from ' + @DBName + '.sys.indexes where object_id = @ObjectID and index_id = @IndexID'
                            SET @ParmDef = N'@ObjectId int, @IndexId int, @IndexName sysname output'
                            EXEC sp_executesql @SQLCmdID, @ParmDef, @ObjectId = @ObjectId, @IndexId = @IndexId, @IndexName = @IndexName output
 
                            --select @PartitionCount = count (*)
                            --    from sys.partitions
                            --    where object_id = @ObjectID and index_id = @IndexID
                            SET @SQLCmdID = 'select @PartitionCount = count (*) from ' + @DBName + '.sys.partitions where object_id = @ObjectID and index_id = @IndexID'
                            SET @ParmDef = N'@ObjectId int, @IndexId int, @PartitionCount int output'
                            EXEC sp_executesql @SQLCmdID, @ParmDef, @ObjectId = @ObjectId, @IndexId = @IndexId, @PartitionCount = @PartitionCount output
 
                            --ReOrg
                            SET @SQLCmdBk = NULL
                            IF @frag < @ReBuildTheshold
                                BEGIN
                                    SET @SQLCmdBk = 'alter index ' + @IndexName + ' on [' + @DBName + '].' + @SchemaName + '.' + @ObjectName + ' reorganize'
                                END
                            IF @frag >= @ReBuildTheshold
                                BEGIN
                                    SET @SQLCmdBk = 'alter index ' + @IndexName + ' on [' + @DBName + '].' + @SchemaName + '.' + @ObjectName + ' rebuild'
                                END
 
                            --set options
                            IF @FillFactor IS NOT NULL SET @SQLCmdFill = 'fillfactor = ' + CAST(@FillFactor AS VARCHAR(3))+ ', '
                            IF @Online = 'true' AND @OnlineReq = 'true' SET @SQLCmdOnline = 'online = on, '
                            IF @PAD_Index = 'true' SET @SQLCmdPad = 'PAD_Index = on, '
                            IF @SortInTempDB = 'true' SET @SQLCmdSort = 'Sort_in_TempDB = on, '
 
                            IF @PartitionCount > 1 SET @SQLCmdBk = @SQLCmdBk + ' partition = ' + CAST(@partitionnum AS nvarchar(10))
 
                            SET @SQLCmdWith = ' with ('
 
                            --With options only apply to rebuilds, not to re-org
                            IF @frag >= @ReBuildTheshold
                                BEGIN
                                    IF @SQLCmdFill IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdFill
                                    IF @SQLCmdOnline IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdOnline
                                    IF @SQLCmdPad IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdPad
                                    IF @SQLCmdSort IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdSort
                                END
 
                            IF @SQLCmdWith <> ' with (' SET @SQLCmdWith = LEFT(@SQLCmdWith, len(@SQLCmdWith) - 1) + ')'
                            IF @SQLCmdWith <> ' with (' SET @SQLCmd = @SQLCmdBk + @SQLCmdWith
                            ELSE SET @SQLCmd = @SQLCmdBk
 
                            --Print and execute
                            EXEC (@SQLCmd)
                            print @SQLCmd
 
                            fetch NEXT FROM CurPartitions INTO @ObjectID, @IndexID, @ParititionNum, @Frag
                        END --CurPartitions
                    close CurPartitions
                    deallocate CurPartitions
                    DROP TABLE #Fragmentation
 
                    --If DB was in Full or Bulk_Logged and tlogging was disabled, then re-enable
                    IF @RecoveryModel IN ('FULL', 'BULK_LOGGED') AND @ChangeRecoveryModel = 'true'
                        BEGIN
                            SET @SQLCmdRecovery = 'alter database ' + @DBName + ' set recovery ' + @RecoveryModel + ' with no_wait'
                            print @DBName + ' recovery model set to ' + @RecoveryModel + ' recovery model.'
                            EXEC (@SQLCmdRecovery)
                        END
                    IF @Exit = 'true' RETURN
 
                fetch NEXT FROM DatabaseNames INTO @DBName
            END --DatabaseNames
    close DatabaseNames
    deallocate DatabaseNames