MonitorDatabaseChanges

CREATE TABLE [dbo].[CaptureSchemaChange](
    [EventType] [nvarchar](max) NULL,
    [SchemaName] [nvarchar](max) NULL,
    [ObjectName] [nvarchar](max) NULL, 
    [ObjectType] [nvarchar](max) NULL,
    [EventDate] [datetime] NULL,
    [SystemUser] [varchar](100) NULL,
    [CurrentUser] [varchar](100) NULL,
    [OriginalUser] [varchar](100) NULL,
    [DatabaseName] [varchar](100) NULL,
    [CommandText] [nvarchar](max) NULL,
    [EventData] [xml] NULL,
    [HostName] [varchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


GO


SET ANSI_PADDING OFF


GO


ALTER TABLE [dbo].[CaptureSchemaChange] ADD  DEFAULT (host_name()) FOR [HostName]
----------------------------------------------------------------------------------


CREATE TRIGGER [trgCaptureDatabaseChange] ON DATABASE


FOR DDL_DATABASE_LEVEL_EVENTS


AS


SET NOCOUNT ON


DECLARE @EventType NVARCHAR(MAX)


DECLARE @SchemaName NVARCHAR(MAX)


DECLARE @ObjectName NVARCHAR(MAX)


DECLARE @ObjectType NVARCHAR(MAX)


DECLARE @DBName VARCHAR(100)


DECLARE @Message VARCHAR(1000)


DECLARE @TSQL NVARCHAR(MAX)


SELECT


@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(MAX)')


,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(MAX)')


,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(MAX)')


,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','NVARCHAR(MAX)')


,@DBName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(MAX)')


,@TSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)')


IF @SchemaName = ' '


BEGIN


SELECT


@SchemaName = default_schema_name


FROM


sys.sysusers SysUser


INNER JOIN


sys.database_principals Pri


ON


SysUser.uid = Pri.principal_id


WHERE


SysUser.name = CURRENT_USER


END


INSERT INTO [CaptureSchemaChange]


([EventType]


,[SchemaName]


,[ObjectName]


,[ObjectType]


,[EventDate]


,[SystemUser]


,[CurrentUser]


,[OriginalUser]


,[DatabaseName]


,[CommandText]


,[EventData]


)


SELECT


@EventType


, @SchemaName


, @ObjectName


, @ObjectType


, getdate()


, SUSER_SNAME()


, CURRENT_USER


, ORIGINAL_LOGIN()


, @DBName


, @TSQL


, EVENTDATA()


GO


SET ANSI_NULLS OFF


GO


SET QUOTED_IDENTIFIER OFF


GO
----------------------------------------------------------------------------------


ENABLE TRIGGER [trgCaptureDatabaseChange] ON DATABASE