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