USE [DATABASE_NAME] --Insert Database Name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChangeLog](
[LogId] [INT] IDENTITY(1,1) NOT NULL,
[DatabaseName] [VARCHAR](256) NOT NULL,
[EventType] [VARCHAR](50) NOT NULL,
[ObjectName] [VARCHAR](256) NOT NULL,
[ObjectType] [VARCHAR](25) NOT NULL,
[SqlCommand] [VARCHAR](MAX) NOT NULL,
[EventDate] [DATETIME] NOT NULL,
[LoginName] [VARCHAR](256) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ChangeLog]
ADD CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (GETDATE()) FOR [EventDate]
GO
USE [DATABASE_NAME] --Insert Database Name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [backup_objects]
ON DATABASE
FOR CREATE_PROCEDURE,
ALTER_PROCEDURE,
DROP_PROCEDURE,
CREATE_TABLE,
ALTER_TABLE,
DROP_TABLE,
CREATE_FUNCTION,
ALTER_FUNCTION,
DROP_FUNCTION,
CREATE_VIEW,
ALTER_VIEW,
DROP_VIEW
AS
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
GO
ENABLE TRIGGER [backup_objects] ON DATABASE
GO