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