EventLog_Capture - monthly job to create tables and modify view

EventLog_Monthly_Table_View_Maintenance.sql

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[EventLog_Monthly_Table_View_Maintenance]
AS 
--mdb 2015/05/06 adding insert_datetime so that we can tell when the heck rows are added, for troubleshooting
DECLARE @sql VARCHAR(8000)
SELECT @sql =
'CREATE TABLE [dbo].[EventLog_' + CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112) + '] (
	[EventLog] [varchar](255) NOT NULL,
	[RecordNumber] [int] NOT NULL,
	[TimeGenerated] [datetime] NOT NULL,
	[TimeWritten] [datetime] NULL,
	[EventID] [int] NULL,
	[EventType] [int] NULL,
	[EventTypeName] [varchar](255) NULL,
	[EventCategory] [int] NULL,
	[EventCategoryName] [varchar](255) NULL,
	[SourceName] [varchar](255) NULL,
	[Strings] [varchar](255) NULL,
	[ComputerName] [varchar](255) NOT NULL,
	[SID] [varchar](255) NULL,
	[Message] [varchar](255) NULL,
	insert_datetime datetime,
PRIMARY KEY CLUSTERED 
(
	[TimeGenerated] ASC,
	[ComputerName] ASC,
	[RecordNumber] ASC,
	[EventLog] ASC
)
) 
'
EXEC (@sql)

SELECT @sql = 'ALTER TABLE [dbo].[EventLog_'+ CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112) + ']  WITH CHECK ADD CHECK  ([TimeGenerated]>='''
 + CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE() )),101) 
 + ''' AND [TimeGenerated]< ''' 
 + CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,2,GETDATE() )),101)
 + ''')'
EXEC (@sql)



SELECT @sql = 'alter view EventLog_Details as '

SELECT @sql = @sql + CHAR(13)
			+ 'select EventLog,
		RecordNumber,
		TimeGenerated,
		TimeWritten,
		EventID,
		EventType,
		EventTypeName,
		EventCategory,
		EventCategoryName,
		SourceName,
		Strings,
		ComputerName,
		SID,
		[Message],
		insert_datetime
		 from ' + table_name + CHAR(13) 
			+ 'union all' 
		FROM INFORMATION_SCHEMA.tables 
		WHERE table_NAME LIKE 'EventLog_20[0-9][0-9][0-9][0-9]' 
		AND table_name NOT LIKE 'EventLog_'+ CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112)
ORDER BY TABLE_NAME

SELECT @sql = @sql + CHAR(13) + 'SELECT * FROM EventLog_'+ CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112)

EXEC (@sql)

GO