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.
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.