변경된 sp 로그 관리

CREATE TABLE dbo.SYS_PROC_LOG(

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

[OBJECT_NM] [varchar](100) NULL,

[OBJECT_TYPE] [varchar](20) NULL,

[SQLCMD] [text] NULL,

[UPDATER] [varchar](20) NULL,

[UPDATEDATE] [datetime] NULL,

 CONSTRAINT XPKSPLOG PRIMARY KEY NONCLUSTERED 

(

SERIAL_NO ASC

))

GO

CREATE TRIGGER [TRG_SYS_PROC_LOG] 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.SYS_PROC_LOG (OBJECT_NM, OBJECT_TYPE, SQLCMD, UPDATER, UPDATEDATE)

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(8000)'),

 HOST_NAME(),

 GETDATE())

GO

=============================================================================================

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())

ps.

필드가 TEXT 필드 일 경우  VARCHAR(8000)

출처 :http://www.sqler.com/491522