sql_ddl_audit

CREATE TABLE [dbo].[Audit_Info](
	[EventTime] [datetime] NULL,
	[LoginName] [varchar](255) NULL,
	[UserName] [varchar](255) NULL,
	[HostName] [varchar](255) NULL,
	[ApplicationName] [varchar](255) NULL,
	[DatabaseName] [varchar](255) NULL,
	[SchemaName] [varchar](255) NULL,
	[ObjectName] [varchar](255) NULL,
	[ObjectType] [varchar](255) NULL,
	[DDLCommand] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TRIGGER [Audit_Table_DDL]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
DECLARE       @eventInfo XML
SET           @eventInfo = EVENTDATA()
INSERT INTO Audit.dbo.Audit_Info VALUES
(
     REPLACE(CONVERT(VARCHAR(50),
            @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
     CONVERT(VARCHAR(255),
          @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
     CONVERT(VARCHAR(255),
          @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
     CONVERT(VARCHAR(255),
          @eventInfo.query('data(/EVENT_INSTANCE/HostName)')),
     CONVERT(VARCHAR(255),
          @eventInfo.query('data(/EVENT_INSTANCE/ApplicationName)')),
     CONVERT(VARCHAR(255),
          @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
     CONVERT(VARCHAR(255),
          @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
     CONVERT(VARCHAR(255),
          @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
     CONVERT(VARCHAR(255),
          @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
     EVENTDATA().value  
        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
)
GO

ENABLE TRIGGER [Audit_Table_DDL] ON DATABASE
GO