USE [SQLSrvMonitor] GO /****** Object: StoredProcedure [dbo].[sCheckEachDB08] Script Date: 3/3/2016 7:43:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Create date: 20 Oct 2015 -- Description: Runs DBCC CheckDB for every DB, or one specified, and stores the History -- this version for SQL 2008, not 2012 or later. -- ============================================= ALTER PROCEDURE [dbo].[sCheckEachDB08] @DatabaseName SYSNAME = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --Following few lines for error checking DECLARE @message VARCHAR(255) DECLARE @CntDBs TINYINT = (SELECT COUNT(*) FROM sys.databases WHERE database_id > 4) SELECT @message = 'The number of DBs for crEachDB is: ' + CAST(@CntDBs AS VARCHAR(3)) EXEC xp_logevent 60000, @message, informational; IF @DatabaseName IS NULL -- Run against all databases BEGIN DECLARE crEachDB CURSOR FOR SELECT name FROM sys.databases db WHERE database_id > 4 --AND db.state_desc = 'ONLINE' --AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots) --AND is_read_only = 0 ORDER BY name ASC OPEN crEachDB FETCH next FROM crEachDB INTO @DatabaseName WHILE @@FETCH_STATUS=0 BEGIN SELECT @message = @DatabaseName + ' is being checked.' EXEC xp_logevent 60000, @message, informational; INSERT INTO SQLSrvMonitor.dbo.tDBCCHistory ( [Error],[Level],[State],[MessageText],[RepairLevel],[Status],[DbId], [ObjectID],[IndexId],[PartitionID],[AllocUnitID],[File], [Page],[Slot],[RefFile],[RefPage],[RefSlot],[Allocation] ) EXEC ('dbcc checkdb(''' + @DatabaseName + ''') with tableresults') FETCH next FROM crEachDB INTO @DatabaseName END CLOSE crEachDB DEALLOCATE crEachDB END ELSE -- run against a specified database (ie: EXEC sCheckEachDB 'DB Name Here' INSERT INTO SQLSrvMonitor.dbo.tDBCCHistory ( [Error],[Level],[State],[MessageText],[RepairLevel],[Status],[DbId], [ObjectID],[IndexId],[PartitionID],[AllocUnitID],[File], [Page],[Slot],[RefFile],[RefPage],[RefSlot],[Allocation] ) EXEC ('dbcc checkdb(''' + @DatabaseName + ''') with tableresults') END