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