Customised CheckDB script for all databases.

Below is the script for Checkdb, which will extract the database information(Only active databases) and run the checkdb one by one.

You can create a Stored Procedure in MSDB and schedule a job or dirctely you can run it.

declare @dbname sysname  -- this variable holds the name of the database 

DECLARE @SQLVer int -- SQL Server primary version 

 

set @dbname=@DB   

   

IF @dbname is not NULL    

begin   

dbcc checkdb (@dbname)   

return   

end   

 

SELECT @SQLVer = SUBSTRING(@@VERSION, CHARINDEX('-',@@VERSION)+1,2) 

IF @SQLVer <> 9                --Condition to check the version of SQL Server  

begin 

declare dbs cursor for   

select name from master..sysdatabases where   

status & (32 + 64 + 128 + 512 + 1024 + 2048 + 4096 + 32768) = 0  /*  To skip the databases which are  

      32 = loading 

      64 = pre recovery 

      128 = recovering 

      256 = not recovered 

      512 = offline 

      1024 = read only 

      2048 = dbo use only 

      4096 = single user 

      32768 = emergency mode   */ 

 

end  

else 

begin  

declare dbs cursor for   

select name from sys.databases where   

state  & (1 + 2 + 3 + 4 + 5  + 6 ) = 0 and is_read_only <>1    /*making use of the catalog view in SQL Server2005 

     and the state values indicate as below 

     Database state: 

     0 = ONLINE  

     1 = RESTORING 

     2 = RECOVERING 

     3 = RECOVERY_PENDING 

     4 = SUSPECT 

     5 = EMERGENCY 

     6 = OFFLINE            */ 

  

end 

 

declare @str varchar(64) 

open dbs   

fetch next from dbs into @dbname   

   

while @@fetch_status=0   

begin   

set @str='dbcc checkdb (['+@dbname+'])' 

exec(@str) 

--dbcc checkdb (@dbname)   

fetch next from dbs into @dbname   

end   

close dbs   

deallocate dbs