--//=========================================================================================
--// Create a Repository
--//=========================================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Audit].[DDLEventLog](
[DDLEventLogID] [int] IDENTITY(1,1) NOT NULL,
[EventType] [varchar](50) NOT NULL,
[ObjectName] [varchar](255) NOT NULL,
[ObjectType] [varchar](25) NOT NULL,
[SQLCommand] [varchar](max) NOT NULL,
[LoginName] [varchar](255) NOT NULL,
[HostName] [varchar](50) NOT NULL,
[EventXML] [xml] NOT NULL,
[CreatedDate] [datetime] NULL,
CONSTRAINT [PK_AuditDDLEventLog] PRIMARY KEY CLUSTERED
(
[DDLEventLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [Audit].[DDLEventLog] ADD CONSTRAINT [df_AuditDDLEventLog_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
--//=========================================================================================
--// Create a Trace to save all the changes on the new Repository
--//=========================================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [tr_DDLAuditAll]
on database
for DDL_DATABASE_LEVEL_EVENTS
as
set nocount on
declare @data xml
set @data = EVENTDATA()
-- exclude index rebuild and reorganisation from logging (regular maintenance activity can be ignored)
if @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') like 'ALTER INDEX%REBUILD%'
or @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') like 'ALTER INDEX%REORGANIZE%'
or @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') like 'ALTER INDEX%DISABLE%'
or @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') like 'UPDATE STATISTICS%'
return
begin try
insert into Audit.DDLEventLog(
EventType,
ObjectName,
ObjectType,
SqlCommand,
LoginName,
HostName,
EventXML)
select
EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
ObjectName = isnull(@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(127)')+ '.' +@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(127)'), ''),
ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
SQLCommand = case -- remove initial CRLF if it exists
when left(@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 2) = char(13) + char(10) then substring(@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 3, 99999)
else @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
end,
LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(255)'),
HostName = host_name(),
EventXML = @data
end try
begin catch
select
Comment = 'Audit Error',
EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
ObjectName = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(127)')+ '.' +@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(127)'),
ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
SQLCommand = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(255)'),
HostName = host_name(),
EventXML = @data
end catch
GO
ENABLE TRIGGER [tr_DDLAuditAll] ON DATABASE
GO
USE [master]
GO
ALTER DATABASE [TDW] SET READ_WRITE
GO
DISABLE TRIGGER [tr_DDLAuditAll] ON DATABASE
GO