Check DB

/****** Object:  StoredProcedure [dbo].[usp_DBCC_CHECKDB]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_DBCC_CHECKDB]
AS
SET NOCOUNT ON
DECLARE @DbName varchar(32), @Server varchar(255), @SqlCmd varchar(255), @Result INT, @EmailTo varchar(255), @Msg varchar(255), @DBMode varchar(255)
— ———————————————————————————————————————-

SET @EmailTo = ‘you@domain.com’ — CHANGE ME

— ———————————————————————————————————————-
–Check sysmessages for error message ID 70000
DECLARE @SysMsg VARCHAR(255)
SET @SysMsg = ‘DBCC checkdb has returned an error. Check consistency for database %s’
SET @Result = (SELECT COUNT(*) FROM master..sysmessages WHERE error = 70000)
IF @Result = 0
 BEGIN
 EXEC master..sp_addmessage 70000, 16, @SysMsg
 END
IF @Result = 1
 BEGIN
 SET @Result = (SELECT COUNT(*) FROM master..sysmessages WHERE error = 70000 AND description LIKE @SysMsg)
 IF @Result = 0
  BEGIN
   EXEC sp_dropmessage 70000
   EXEC master..sp_addmessage 70000, 16, @SysMsg
  END
END
— ———————————————————————————————————————-
SELECT @Server = LTRIM(RTRIM(@@servername))
— Create table containing list of databases ONLINE
CREATE TABLE #TEMP (
NAME VARCHAR(255),
STATUS VARCHAR (255))

INSERT INTO #TEMP SELECT NAME, CONVERT(VARCHAR(255),DATABASEPROPERTYEX(name, ‘Status’))
FROM   master.dbo.sysdatabases

CREATE TABLE #DB_LIST(
NAME VARCHAR(255))

INSERT INTO #DB_LIST SELECT NAME FROM #TEMP
WHERE STATUS LIKE ‘ONLINE’ AND NAME NOT LIKE ‘TEMPDB’

DROP TABLE #TEMP
— Loop through all databases running DBCC CHECKDB
DECLARE DBCURSOR CURSOR FOR
 SELECT NAME FROM #DB_LIST
 OPEN DBCURSOR
 FETCH NEXT FROM DBCURSOR INTO @DbName
  WHILE (@@FETCH_STATUS=0)
  BEGIN
   BEGIN
   SELECT @SqlCmd = ‘DBCC CHECKDB(”’ + @DbName + ”’)’ — + ‘ WITH NO_INFOMSGS’
   PRINT @SqlCmd
   EXEC (@SqlCmd)
   SET @Result = @@ERROR
   IF @Result <> 0
     BEGIN
     SET @Msg =  ‘DBCC consistency error: ‘ + @DbName 
     RAISERROR (70000, 16, 1, @DbName, @Msg) WITH LOG
           EXEC master..sp_send_cdosysmail @server,@EmailTo,’DBCC Error’,@Msg
     END
   ELSE
    BEGIN
            PRINT ‘No error reported by DBCC checkdb for ‘ + @DbName
    END
  END
 FETCH NEXT FROM DBCURSOR INTO @DbName
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
DROP TABLE #DB_LIST

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s