MaintenanceIndex

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE MaintenanceIndex
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @sInstruction NVARCHAR(MAX)
    SELECT @sInstruction = N'SELECT * INTO ##FragmentedIndexes FROM ('

    SELECT
        @sInstruction = @sInstruction + N' SELECT N''' + s.name + '.' + t.name + ''' AS TargetTable, name AS TargetIndex, N''ALTER INDEX '' + name + N'' ON ' + s.name + '.' + t.name + ' ''
            + CASE WHEN avg_fragmentation_in_percent > 30 THEN ''REBUILD'' ELSE ''REORGANIZE'' END as ExecuteMe
            FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N''' + s.name + N'.' + t.name + N'''), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE avg_fragmentation_in_percent > 5 AND name IS NOT NULL UNION ALL'
    FROM
        sys.tables T
        JOIN sys.schemas S ON S.schema_id = T.schema_id

    SELECT @sInstruction = @sInstruction + N' SELECT N'''', N'''', N'''' WHERE 0 = 1) AS T'

    EXEC (@sInstruction)
    SELECT * FROM ##FragmentedIndexes

    SELECT @sInstruction = N''
    SELECT @sInstruction = @sInstruction + '
    ' + ExecuteMe FROM ##FragmentedIndexes

    --SELECT @sInstruction
    EXEC (@sInstruction)

    DROP TABLE ##FragmentedIndexes

    -- Shrink des logs.
    SELECT @sInstruction = N''
    SELECT @sInstruction = @sInstruction + N'
    DBCC SHRINKFILE (N''' + name + ''', 0)'
    FROM sysfiles WHERE groupid = 0

    EXEC (@sInstruction)
END
GO