Quick script to run DBCC CheckDB for all of your databases

Occasionally you may want to run DBCC CheckDB against all of the databases on your SQL Server. Hopefully you have a job to run checkdB regularly, but in case you just want to check to confirm that you have no corruption on all of your databases right now you can use this script.

SELECT name, 'GO
PRINT ''Checking Database ' + name + ''';
GO
DBCC CHECKDB(''' + name + ''') with NO_INFOMSGS, DATA_PURITY;' as script 
FROM sys.databases

Once you run the script copy everything from the ‘script’ column and paste that into management studio, and there you have it, a quickly generated script to run CheckDB against all your databases.

The output will look something like this:

And when you copy and paste the “script” column will get will something like this:

GO
PRINT 'Checking Database master';
GO
DBCC CHECKDB('master') with NO_INFOMSGS, DATA_PURITY;
GO
PRINT 'Checking Database tempdb';
GO
DBCC CHECKDB('tempdb') with NO_INFOMSGS, DATA_PURITY;
GO
PRINT 'Checking Database model';
GO
DBCC CHECKDB('model') with NO_INFOMSGS, DATA_PURITY;
GO
PRINT 'Checking Database msdb';
GO
DBCC CHECKDB('msdb') with NO_INFOMSGS, DATA_PURITY;
GO
PRINT 'Checking Database QueryTraining';
GO
DBCC CHECKDB('QueryTraining') with NO_INFOMSGS, DATA_PURITY;

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!