How to send db_mail only the erroroneous entries/

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  April 14, 2015 at 12:22 pm

  #296889

  Declare @columnList nvarchar(max)

  DECLARE @sqlCommand varchar(max)

  DECLARE @like varchar(max)

  --set quoted_identifier ON

  SET @columnList = 'DBNAME, MessageText, ObjName, Error, TimeStamp' -- Include Columns of your interest.

  SET @like = '''%checkdb found%'''

  SET @sqlCommand = 'SELECT ' + @columnList + ' FROM DB_ADMIN..DBCC_CHECK_HISTORY WHERE MessageText LIKE ' + @like

  EXEC (@sqlCommand)

  The above query lists all good and bad entries. I need to only filter out where ever it has Errors (both allocation/consistency). Please suggest how to frame the above query. Thnx

  CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2012'.

  CHECKDB found 0 allocation errors and 3 consistency errors in table 'sales' (object ID 2073058421).

  CHECKDB found 0 allocation errors and 0 consistency errors in database 'ReportServerTempDB'.

  CHECKDB found 1 allocation errors and 4 consistency errors in database 'DemoRestoreOrRepair'.

  Thanks.