/* Katherine Villyard, posted 8/31/12
http://www.katherinevillyard.com/2012/08/31/windows-internal-database-maintenance/
*/

Declare @DBname varchar(255),
		@RowsToProcess int,
		@CurrentRow int,
		@SelectCol1 varchar(max), 
		@SQL nvarchar(max)

CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), dbname nvarchar(max) )  
INSERT into #maintenancetemp (dbname) select name from sys.databases where name <> 'tempdb' and name <> 'model'
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT 
        @SelectCol1=dbname
        FROM #maintenancetemp
        WHERE RowID=@CurrentRow
				set @DBname = @SelectCol1				
				set @SQL='DBCC CHECKDB(N''' + @DBName+ ''')  WITH NO_INFOMSGS'
				EXEC SP_EXECUTESQL @SQL				
				set @SQL='USE ['+@DBname+']; exec sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'', '' '', 80)"; EXEC sp_updatestats'
				EXEC SP_EXECUTESQL @SQL				
END

drop table #maintenancetemp


