Defragmentation and update statistics for all online databases

USE [msdb]
GO

/****** Object:  Job [defragmentation_and_statistics]    Script Date: 3/4/2014 10:55:14 AM ******/BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 3/4/2014 10:55:14 AM ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'defragmentation_and_statistics', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [step1:defragmentation]    Script Date: 3/4/2014 10:55:14 AM ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1:defragmentation', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@on_success_step_id=0, 
@on_fail_action=4, 
@on_fail_step_id=2, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'/* Query developed by Vimal Lohani For reorganizing and rebuilding indexes *//* Query work for all Online Databases in one go *//* Work for index name available other wise will say INDEX NAME NOT SET OR NULL *//* Work for user define indexes *//* Still work if any error occored with indexes */

USE MASTER
GO

SET NOCOUNT ON
If exists (select * from tempdb.sys.all_objects where name like ''#Databases'') 
begindrop table #Databases end
 SELECT ROW_NUMBER() OVER(ORDER BY name) Seq, name Banco
INTO #Databases
FROM sys.databases
WHERE name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')  --Not including system databases
         and name NOT LIKE ''ReportServer%''
AND compatibility_level > 80           --For Database sql 2005 or higher
AND state=0                 --fOR All online databases
ORDER BY Banco;

DECLARE
    @Loop INT = 1,
@Qt INT = (SELECT COUNT(1) FROM #Databases),                --max no of databases
@Banco VARCHAR(50);

WHILE @Loop <= @Qt
 BEGIN
  SET @Banco = (SELECT Banco FROM #Databases WHERE Seq = @Loop);
EXEC( 
''USE '' + @Banco + ''; '' +
''PRINT ''''For Database : '''' + db_name();
Declare @dbid int;
set @dbid=DB_ID();
SELECT
ROW_NUMBER() OVER(ORDER BY p.object_id, p.index_id) Seq,
   t.name Tabela, h.name Esquema,
i.name Indice, p.avg_fragmentation_in_percent Frag
INTO #Consulta
FROM
sys.dm_db_index_physical_stats(@dbid,null,null,null,null) p
join sys.indexes i on (p.object_id = i.object_id and p.index_id = i.index_id)
join sys.tables t on (p.object_id = t.object_id)
join sys.schemas h on (t.schema_id = h.schema_id)
join sys.objects o on (o.object_id=p.object_id)
where p.avg_fragmentation_in_percent > 5.0
and p.index_id >= 0                                    --all indexes including heap
and p.page_count >= 10
AND o.type=''''U''''                                  
ORDER BY Esquema, Tabela;
DECLARE
@Loop INT = 1,
@Total INT = (SELECT COUNT(1) FROM #Consulta),
@Comando VARCHAR(500),
@Updato VARCHAR(500)
WHILE @Loop <= @Total
BEGIN
SELECT @Comando = ''''ALTER INDEX '''' + Indice +
'''' ON '''' + Esquema + ''''.'''' + Tabela +
( CASE WHEN Frag > 45.0 THEN '''' REBUILD'''' ELSE '''' REORGANIZE'''' END)
FROM #Consulta
WHERE Seq = @Loop;
EXEC(@Comando);
PRINT ''''Executed: '''' + ISNULL(@Comando,''''INDEX NAME NOT SET OR NULL'''');
SELECT @Updato=''''UPDATE STATISTICS ''''+ Esquema + ''''.'''' +Tabela + '''' ''''+Indice FROM #Consulta
WHERE Seq = @Loop;
EXEC(@Updato);
PRINT ''''UPDATE STATISTICS: ''''+ ISNULL(@Updato,''''INDEX NAME NOT SET OR NULL'''');
SET @Loop = @Loop + 1;
END;
PRINT DB_NAME() + '''' Total No of indexes defragmented : '''' + CONVERT(VARCHAR(5),@Total);
PRINT ''''------------------------------------------------------------------------------'''';
DROP TABLE #Consulta;'');  
  SET @Loop = @Loop + 1;
 END;

DROP TABLE #Databases;
', 
@database_name=N'master', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [step2: update statistics]    Script Date: 3/4/2014 10:55:14 AM ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step2: update statistics', 
@step_id=2, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=1, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'
DECLARE @SQL VARCHAR(1000) 
DECLARE @DB sysname 

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR 
   SELECT [name] 
   FROM master..sysdatabases
   WHERE [name] NOT IN (''model'', ''tempdb'')
   ORDER BY [name]
    
OPEN curDB 
FETCH NEXT FROM curDB INTO @DB 
WHILE @@FETCH_STATUS = 0 
   BEGIN 
       SELECT @SQL = ''USE ['' + @DB +'']'' + CHAR(13) + ''EXEC sp_updatestats'' + CHAR(13) 
       PRINT @SQL 
   exec(@sql)
   Select @SQL= ''USE ['' + @DB +'']'' + CHAR(13) + ''alter database ['' + @DB +'']'' +'' set multi_user''+ CHAR(13)
       PRINT @SQL
   exec(@SQL)
   print ''------------------------------------------------''
   FETCH NEXT FROM curDB INTO @DB 
   END 
   
CLOSE curDB 
DEALLOCATE curDB


CLOSE curDB 
DEALLOCATE curDB', 
@database_name=N'master', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO