-- 1. create job
--    Details in: http://msdn.microsoft.com/en-us/library/ms182079.aspx
USE msdb
EXEC sp_add_job @job_name = N'snRebuildIndexes',
    @enabled = 1,
    @description = N'Rebuild indexes for SecretNet database',
    @notify_level_eventlog = 2,
    @notify_level_netsend =2,   -- notify on failure
    @notify_level_page = 2      -- notify on failure
go

-- 2. 
USE msdb
EXEC sp_add_jobserver @job_name = 'snRebuildIndexes' -- , @server_name = N'ws702z'
go

-- 3. 
USE msdb
EXEC sp_add_jobstep @job_name = N'snRebuildIndexes',
    @step_name = N'Rebuild SecretNet indexes',
    @subsystem = N'TSQL',
    @on_success_action = 1,  -- exit
    @retry_attempts = 5,     -- number of retry attempts
    @retry_interval = 5,      -- interval to next attempt in minutes
--    @database_name=N'SN7_SERVER_SCHEMA', 
    @command = N'begin \
    declare @databaseName sysname = N''SN7_SERVER_SCHEMA''; \
    declare @rebuildFloor float = 40; \
\
    declare @schemaName sysname; \
    declare @tableName  sysname; \
    declare @indexName  sysname; \
    declare @fragmentation float; \
\
    declare @command nvarchar(500); \
\
    declare indexCursor cursor fast_forward local for \
    select s.name schema_name, t.name table_name, i.name index_name, d.avg_fragmentation_in_percent fragmentation \
    from   sys.dm_db_index_physical_stats( DB_ID(@databaseName), null, null, null, null) d \
           inner join sys.tables  t on d.object_id = t.object_id \
           inner join sys.schemas s on t.schema_id = s.schema_id \
           inner join sys.indexes i on d.object_id = i.object_id AND d.index_id = i.index_id \
    where  d.index_id > 0 \
           and d.avg_fragmentation_in_percent > 10 \
           and d.page_count > 8 \
\
    open indexCursor; \
\
    while( 1=1 ) \
    begin \
        fetch next from indexCursor into @schemaName, @tableName, @indexName, @fragmentation; \
        if @@FETCH_STATUS <> 0 break; \
\
        begin try \
            set @command = N''ALTER INDEX '' + @indexName + N'' ON '' + @databaseName + N''.'' + @schemaName + N''.'' + @tableName; \
            if @fragmentation < @rebuildFloor \
            begin \
                set @command = @command + N'' REORGANIZE;''; \
                set @command = @command + N'' UPDATE STATISTICS '' + @databaseName + N''.'' + @schemaName + N''.'' + @tableName + N'' '' + @indexName + N'';''; \
            end \
            else \
            begin \
                set @command = @command + N'' REBUILD WITH (ONLINE = ON); ''; \
            end; \
\
            exec (@command); \
        end try \
        begin catch \
            continue; \
        end catch \
    end; \

    close indexCursor; \
    deallocate indexCursor; \
end;'
GO

-- 6.1. set job schedule
--  details in http://msdn.microsoft.com/en-us/library/ms187358.aspx 
USE msdb
EXEC sp_add_jobschedule @job_name = N'snRebuildIndexes', 
    @name = N'ScheduledSecretNetRebuildIndexes',
    @freq_type = 4, --daily
    @freq_interval = 1, --once
    @active_start_time = '000100' -- (00:01) 24hr HHMMSS.
go

-- 6.2. start immediately
--USE msdb
--EXEC sp_start_job @job_name = N'snRebuildIndexes'