트리거를 이용한 DDL 추적

테이블 생성

 

CREATE TABLE [dbo].[DDLLog](

       [DDLLogIDX] [int] IDENTITY(1,1) NOT NULL,

       [PostTime] [datetime] NULL,

       [DB_User] [nvarchar](100) NULL,

       [Event] [nvarchar](100) NULL,

       [ObjectName] [nvarchar](100) NULL,

       [ObjectType] [nvarchar](100) NULL,

       [TSQL] [nvarchar](2000) NULL,

       [SPID] [int] NULL,

       [ServerName] [nvarchar](200) NULL,

       [LoginName] [nvarchar](100) NULL,

       [UserName] [nvarchar](100) NULL,

       [HostName] [nvarchar](100) NULL,

       [Program] [nvarchar](100) NULL,

       [DatabaseName] [nvarchar](200) NULL,

       [XMLData] [xml] NULL

) ON [PRIMARY]

 

GO

 

 

트리거 생성

 

CREATE TRIGGER [TRI_DDLLog]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

 

DECLARE @data XML

SET @data = EVENTDATA()

DECLARE @SPID INT

SET @SPID = @data.value('(/EVENT_INSTANCE/SPID)[1]','int')

 

INSERT dbo.DDLLog

(

       PostTime

,      DB_User

,      Event

,      ObjectName

,      ObjectType

,      TSQL

,      SPID

,      ServerName

,      LoginName

,      UserName

,      HostName

,      Program

,      DatabaseName

,      XMLData

)

SELECT

       PostTime            = GETDATE()

,      DB_User                    = CONVERT(nvarchar(100), CURRENT_USER)

,      Event               = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

,      ObjectName          = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')

,      ObjectType          = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)')

,      TSQL                = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

,      SPID                = @data.value('(/EVENT_INSTANCE/SPID)[1]','int')

,      ServerName          = @data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(200)')

,      LoginName           = @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)')

,      UserName            = @data.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)')

,      HostName            = (SELECT hostname FROM sys.sysprocesses WHERE spid = @SPID)

,      Program                    = (SELECT program_name FROM sys.sysprocesses WHERE spid =@SPID)

,      DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(200)')

,      XMLData                    = @data

 

 

GO

 

원하시는 DB에 트리거를 생성 후 DDL 문을 실행하면 해당 내역을 DB로 남기는 것 입니다.

 

아래는 테스트 스크립트 입니다.

 

--//1

CREATE TABLE TB1

(

       COL1   INT    NOT NULL

)

GO

 

--//2

ALTER TABLE TB1 ADD COL2 INT

GO

 

--//3

DROP TABLE TB1

GO

 

--//4

SELECT * FROM DDLLog

GO

ALTER

 

아래와 같은 결과를 보실 수 있으며 XMLData 컬럼을 클릭하시면 XML로 된 DDL 내역을 보실 수 있습니다.

 

http://www.sqler.com/427945