Tara Kizer Blog

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_DBCC_CHECKDB
--
-- AUTHOR               : Tara Duggan
-- DATE     : May 3, 2004
--
-- INPUTS    : None
-- OUTPUTS    : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure runs DBCC CHECKDB for each of the databases.
--
-- EXAMPLES (optional)  : EXEC isp_DBCC_CHECKDB
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- 02/07/2007 Tara Kizer
-- Added support for SQL Server 2005 (didn't want to use sysdatabases since it's only there for
-- backward compatibility.
--
-- Added the option to skip databases that are in a loading state, such as those with the NORECOVERY
-- option set or those that are the mirror in database mirroring.
----------------------------------------------------------------------------------------------------
ALTER PROC [dbo].[isp_DBCC_CHECKDB]
AS

SET NOCOUNT ON

DECLARE @dbid int, @DBName sysname, @SQL nvarchar(4000), @version char(1)

SELECT @dbid = 0, @version = CONVERT(char(1), SERVERPROPERTY('ProductVersion'))

IF @version = '8'
BEGIN
 WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases WHERE dbid > @dbid AND status & 32 = 0)
 BEGIN
  SELECT TOP 1 @dbid = dbid, @DBName = name
  FROM master.dbo.sysdatabases
  WHERE dbid > @dbid AND status & 32 = 0
  ORDER BY dbid

  SET @SQL = 'DBCC CHECKDB([' + @DBName + '])'

  EXEC sp_executesql @statement = @SQL
 END
END
ELSE IF @version = '9'
BEGIN
 WHILE @dbid < (SELECT MAX(database_id) FROM master.sys.databases WHERE database_id > @dbid AND state IN (0, 4))
 BEGIN
  SELECT TOP 1 @dbid = database_id, @DBName = name
  FROM master.sys.databases
  WHERE database_id > @dbid AND state IN (0, 4)
  ORDER BY database_id

  SET @SQL = 'DBCC CHECKDB([' + @DBName + '])'

  EXEC sp_executesql @statement = @SQL
 END
END