Cry How to...
Run dbcc checkdb on each database (using cursors)
The simplest way to run '
The disadvantage of this approach is that the script needs to be updated each time a new database is added or a database deleted. It is also unlikely to be portable from one server to another without being rewritten.
An alternate and more adaptable approach is to let the script determine what databases are present and to run dbcc checkdb on each in turn:
The advantage of this approach is that the script does not need updating should a new database be added or one removed and it can be copied from one server to another and executed without change.
If I am running this script from SQL Server Management Studio then I tend to substitute:
RaisError('Checking database %s.',0,1,@database_name) with nowait
in the above script as this means you can see any output in the "Messages" tab as the script runs instead of having to wait for it to complete.
These notes have been tested against SQL Server 2008, 2005, 2000 and SQL Server 7.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.