USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[ddl_log](
    [DDL_Log_GUID] [uniqueidentifier] NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DB_Login] [nvarchar](255) NULL,
    [DB_User] [nvarchar](255) NULL,
    [Event] [nvarchar](255) NULL,
    [TSQL] [nvarchar](max) NULL,
 CONSTRAINT [PK_ddl_log] PRIMARY KEY CLUSTERED 
(
    [DDL_Log_GUID] 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 [srv].[ddl_log] ADD  CONSTRAINT [DF_ddl_log_DDL_Log_GUID]  DEFAULT (newid()) FOR [DDL_Log_GUID]
GO

USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[ddl_log_all](
    [DDL_Log_GUID] [uniqueidentifier] NOT NULL,
    [Server_Name] [nvarchar](255) NOT NULL,
    [DB_Name] [nvarchar](255) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DB_Login] [nvarchar](255) NULL,
    [DB_User] [nvarchar](255) NULL,
    [Event] [nvarchar](255) NULL,
    [TSQL] [nvarchar](max) NULL,
    [InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ddl_log_all] PRIMARY KEY CLUSTERED 
(
    [DDL_Log_GUID] 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 [srv].[ddl_log_all] ADD  CONSTRAINT [DF_ddl_log_all_DDL_Log_GUID]  DEFAULT (newid()) FOR [DDL_Log_GUID]
GO

ALTER TABLE [srv].[ddl_log_all] ADD  CONSTRAINT [DF_ddl_log_all_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [SchemaLog] 
ON DATABASE --ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    DECLARE @data XML
    begin try
    if(CURRENT_USER<>'NT AUTHORITY\NETWORK SERVICE' and SYSTEM_USER<>'NT AUTHORITY\NETWORK SERVICE')
    begin
        SET @data = EVENTDATA();
        INSERT srv.ddl_log(
                    PostTime,
                    DB_Login,
                    DB_User,
                    Event,
                    TSQL
                  ) 
        select 
                    GETUTCDATE(),
                    CONVERT(nvarchar(255), SYSTEM_USER),
                    CONVERT(nvarchar(255), CURRENT_USER), 
                    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)'), 
                    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
        where       @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)') not in('UPDATE_STATISTICS', 'ALTER_INDEX')
                and @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') not like '%Msmerge%'; --не нужно следить за изменения объектов репликации
    end
    end try
    begin catch
    end catch

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [SchemaLog] ON DATABASE
GO