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