Run DBCC CHECKDB Against All Databases on Server

This script runs DBCC CHECKDB against every database on the server. It starts off with the simplest and fastest commands, and works up the the most stringent test. Note that TABLOCK keyword is commented out - you can use it at your own discretion. The commands that actually repair the database can't be run accidentally, and the version that can allow data loss - aptly named REPAIR_ALLOW_DATA_LOSS - is left as an exercise for the reader. (In other words, I'm not giving you that particular big, sharp knife!)

-- Run DBCC CHECKDB commands against all databases, including system. 
-- Read-only checks are run automatically. For safety's sake, to run 
-- the "repair" checks, you have to highlight that section and run it. 
-- SQL Server 2005+ 

set nocount on
declare @Sql nvarchar(4000)


--------------------------------------- 
--           READ-ONLY CHECKS           
--------------------------------------- 

-- Do quickest check possible. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', NOINDEX) WITH /* ?? TABLOCK, */ PHYSICAL_ONLY, ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- Now check things beside what PHYSICAL_ONLY does: the integrity of the 
-- physical structure of the page and record headers, and the consistency 
-- between the pages' object ID and index ID and the allocation structures. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', NOINDEX) WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- Now also check the indexes. This is the most thorough check possible. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'') WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

go


--------------------------------------- 
--           READ-WRITE CHECKS          
--                                      
-- "Use the REPAIR options only as a    
-- last resort. To repair errors, we    
-- recommend restoring from a backup."  
--------------------------------------- 

raiserror('    *** THESE ARE READ-WRITE CHECKS - BE SURE YOU KNOW WHAT YOU ARE DOING!', 16, 1) with nowait

set nocount on
declare @Sql nvarchar(4000)

-- Performs both minor, quick repairs, such as repairing extra keys in 
-- nonclustered indexes, and time-consuming repairs such as rebuilding 
-- indexes. These repairs can be performed without risk of data loss. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', REPAIR_REBUILD) WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- If you want to use REPAIR_ALLOW_DATA_LOSS, you'll have to write your own command! 

go