-- script to log schema changes
CREATE TABLE ddl_log
(
ProgramName VARCHAR(100) ,
PostTime DATETIME ,
username VARCHAR(100) ,
Event NVARCHAR(100) ,
TSQL NVARCHAR(MAX)
)
GO
--Creation of the trigger to audit the data on the specific database. This collects data
--for the Alter database events only.
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'monitoring_ddl')
DROP TRIGGER monitoring_ddl
ON DATABASE
GO
CREATE TRIGGER monitoring_ddl
ON DATABASE
FOR ALTER_TABLE, CREATE_TABLE, DROP_TABLE,
ALTER_VIEW, CREATE_VIEW, DROP_VIEW,
ALTER_PROCEDURE, CREATE_PROCEDURE, DROP_PROCEDURE,
ALTER_FUNCTION, CREATE_FUNCTION, DROP_FUNCTION,
ALTER_INDEX, CREATE_INDEX, DROP_INDEX
AS
SET ANSI_PADDING ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(ProgramName, PostTime, username, Event, TSQL)
VALUES
(program_name(), GETDATE(),
suser_sname(),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') );
GO