Search
Duplicate
🗃

MSSQL DDL을 이용한 소스코드관리

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())
SQL
복사
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)')) );
SQL
복사