CREATE TABLE dbo.SPLOG(
    일련번호 int IDENTITY(1,1) NOT NULL,
    오브젝트명 varchar(100) NULL,
    구분 varchar(20) NULL,
    SQLCMD varchar(max) NULL,
    수정자 varchar(20) NULL,
    수정일 datetime NULL,
    CONSTRAINT XPKSPLOG PRIMARY KEY NONCLUSTERED
        (
         일련번호 ASC
            ))
 
GO
 
CREATE TRIGGER TRG_SPLOG ON DATABASE
    FOR
    CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
    CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
    CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
    CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER
 
    AS
 
    DECLARE  @DATA   XML
 
SET @DATA = EVENTDATA()
 
 
INSERT INTO DBO.SPLOG (오브젝트명, 구분, SQLCMD, 수정자, 수정일)
VALUES
(@DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(100)'),
 @DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(100)'),
 @DATA.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(MAX)'),
 HOST_NAME(),
 GETDATE())

CREATE TABLE TABLELOG
(
             DDL_Event_Time            datetime
             ,
             DDL_Login_Name            varchar(150)
             ,
             DDL_User_Name             varchar(150)
             ,
             DDL_Database_Name         varchar(150)
             ,
             DDL_Schema_Name           varchar(150)
             ,
             DDL_Object_Name           varchar(150)
             ,
             DDL_Object_Type           varchar(150)
             ,
             DDL_Command              varchar(max)
);
 
CREATE TRIGGER Audit_DDL
ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
     DECLARE
        @event xml;
     SET
     @event = EVENTDATA();
     INSERT INTO TABLELOG
     VALUES
     (
     REPLACE(CONVERT(varchar(50),
     @event.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/LoginName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/UserName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/DatabaseName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/SchemaName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectType)'))
     ,
     CONVERT(varchar(max),
     @event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
     );