-- does a dbcc checkdb on a database

-- this should be run via sqlcmd with -o parameter sending ouput to file check.log;
-- this file then gets read by check2.sql

declare @errnum int
declare @errdesc varchar(255)
set @errnum=0
set @errdesc=''

begin try
  dbcc checkdb('MYDATABASE')
  set @errnum=@@error
  set @errdesc=error_message()
end try  
begin catch
  set @errnum=@@error
  set @errdesc=error_message()
end catch

if @errnum <> 0 begin
  print 'CHECKDB ERROR: ' + convert(varchar,@errnum) + ' ' + @errdesc
end

-- loads check.log generated by check1.sql into a table and
-- checks if there are errors

set nocount on

-- U = table V = view P = stored procedure FN = scalar function TF = table-valued function
if object_id('tempdb..#sqlcmdlog','U') is not null drop table #sqlcmdlog
create table #sqlcmdlog(line varchar(8000))

-- load log file into a one-column table
bulk insert #sqlcmdlog
from 'c:\temp\check.log'
with (rowterminator = '')
go

-- check if there were errors
declare @count1 int
declare @count2 int

select @count1=count(*)
from #sqlcmdlog
where line like 'CHECKDB found 0 allocation errors and 0 consistency errors%'

select @count2=count(*)
from #sqlcmdlog
where line like 'CHECKDB ERRORS%'

if @count1<>1 or @count2>0 begin

  -- you should send an email here with file check.log as an attachment

  select line
  from #sqlcmdlog
  where line like 'CHECKDB found % allocation errors and % consistency errors%'
    or line like 'CHECKDB ERROR%'

end

declare @databaseName
declare @DBCCCheckDBResults table 
(
	[DBCCCheckDBResultsID] [int] IDENTITY(1,1) NOT NULL,
	[ServerName] [varchar](255) NULL,
	[NodeName] [varchar](255) NULL,
	[DatabaseName] [varchar](255) NULL,
	[Error] [bigint] NULL,
	[Level] [bigint] NULL,
	[State] [bigint] NULL,
	[MessageText] [varchar](8000) NULL,
	[RepairLevel] [bigint] NULL,
	[Status] [bigint] NULL,
	[DbId] [bigint] NULL,
	[ObjectId] [bigint] NULL,
	[IndexId] [bigint] NULL,
	[PartitionId] [bigint] NULL,
	[AllocUnitId] [bigint] NULL,
	[File] [bigint] NULL,
	[Page] [bigint] NULL,
	[Slot] [bigint] NULL,
	[RefFile] [bigint] NULL,
	[RefPage] [bigint] NULL,
	[RefSlot] [bigint] NULL,
	[Allocation] [bigint] NULL,
	[RunDate] [datetime] NULL
)

	insert @DBCCCheckDBResults
	(
		 Error			
		,Level			
		,State       	
		,MessageText 	
		,RepairLevel 	
		,Status      	
		,DbId        	
		,ObjectId     	
		,IndexId       	
		,PartitionId	
		,AllocUnitId	
		,[File]      	
		,Page        	
		,Slot        	
		,RefFile     	
		,RefPage     	
		,RefSlot    	
		,Allocation	
	)	
	exec (''dbcc checkdb (['' + @databaseName + '']) with TABLERESULTS'')