Cry How to...

Run dbcc checkdb on each database (using cursors)

The simplest way to run 'dbcc checkdb' (to check the structural integrity of an SQL Server database) on each database in the system is to write a simple script that includes each database by name, for example:

dbcc checkdb('MASTER') with no_infomsgs
dbcc checkdb('MODEL') with no_infomsgs
.
.
dbcc checkdb('TEMP') with no_infomsgs

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:

declare database_cursor CURSOR for select name from master..sysdatabases
declare @database_name sysname

open database_cursor
fetch next from database_cursor into @database_name
while @@FETCH_STATUS=0
begin
  print @database_name
  dbcc checkdb(@database_name) with no_infomsgs
  fetch next from database_cursor into @database_name
end

close database_cursor
deallocate database_cursor

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

for

print@database_name

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.