SQL: Check Database Cursor
Author: jason
Date: 2014-02-05 11:23:40
Category: Musing

I've run into situations where I can't use a Maintenance Plan or the other DBAs just don't want to use them. Here is a CURSOR script with error handling that walks through each database with the DBCC CHECKDB command

One of the features in the below script is the WITH NO_INFOMSGS after the CHECKDB command. This eliminates the chatty nature of CHECKDB.





Code

DECLARE @Output NVARCHAR(4000) = '', @ErrorCount INT = 0, @dname SYSNAME;
DECLARE dcur CURSOR FOR
SELECT name FROM master.sys.databases ORDER BY database_id DESC
OPEN dcur
FETCH NEXT FROM dcur INTO @dname
WHILE ( @@FETCH_STATUS = 0)
BEGIN
BEGIN TRY
DBCC CHECKDB(@dname) WITH NO_INFOMSGS;
SET @Output = @Output + ' Check DB Done ' + @dname + ', ';
END TRY
BEGIN CATCH
SET @ErrorCount = @ErrorCount + 1
SET @Output = @Output + ' Check DB Error ' + @dname + ERROR_MESSAGE() + ', ';
END CATCH
FETCH NEXT FROM dcur INTO @dname
END
CLOSE dcur
DEALLOCATE dcur
PRINT @Output;
IF @ErrorCount > 0 BEGIN
RAISERROR(@Output, 16, 1);
END


Good article: http://www.sql-server-pro.com/dbcc-checkdb.html



jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009