[SQL]테이블 변경(생성, 수정,삭제) 이력 조회 방법 (Who Created a table?)

MSSQL 2008 이상에 해당되는 이야기입니다.

테이블이 생성되었거나 수정되었거나 삭제 되었는데, 누가 했는지 알수 없을때 아래와 같이 조회 할 수 있습니다.

(*기간이 너무오래지난 것은 볼수없습니다.)

1. ssms 의 기능을 활용 ( 테이블 변경이력 보고서 생성 ) 

db 인스턴스->보고서	표준보고서->스키마변경기록	조회 정보 확인

2. 쿼리로 조회해보기 log.trc 파일 분석 (위의 것과 동일한것인테 테이블을 지정해서 씁니다.)

--CREATE TABLE TEST (ID INT )
DECLARE @DB NVARCHAR(100) = N'TEST'
DECLARE @TBL NVARCHAR(100) = N'TEST'
;WITH TRACE_PATH 
AS (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path FROM sys.traces WHERE is_default = 1)
SELECT  trace.TextData
    ,trace.DatabaseName
    ,trace.ObjectName
    ,TRACE_E.name AS EventName
    ,TRACE_VALUE.subclass_name
    ,trace.EventClass
    ,trace.EventSubClass
    ,trace.StartTime
    ,trace.EndTime
    ,trace.NTDomainName
    ,trace.NTUserName
    ,trace.LoginName
    ,trace.HostName
    ,trace.ApplicationName
    ,trace.Spid
    
FROM TRACE_PATH T_PATH CROSS APPLY	fn_trace_gettable(T_PATH.path, DEFAULT) AS TRACE
					   JOIN			sys.trace_events AS TRACE_E ON TRACE.EventClass=TRACE_E.trace_event_id
					   LEFT JOIN	sys.trace_subclass_values AS TRACE_VALUE ON TRACE_VALUE.trace_event_id = EveNtClass AND TRACE_VALUE.subclass_value = trace.EventSubClass
WHERE
    TRACE_E.name = 'Object:Created'
    AND trace.DatabaseName = @DB
    AND trace.ObjectName = @TBL
    /*
	AND TRACE_VALUE.subclass_name = 'Begin'
	AND TRACE_VALUE.subclass_name = 'Commit'*/
ORDER BY trace.StartTime;

 

누가 테이블을 만들었는지, 지웠는지, 수정했는지 모르때, 어떤 sp, 인덱스, 키설정 등 변경사항이 생겼을때 위방법을 이용해서 조회해 보세요 ~ 

2번의 쿼리는 디폴트 트레이스를 이용하는 것이며 최대 5개 파일 20mb (총 100mb)의 이력 추적이 가능합니다. 최근기준으로 기록되며 과거적재된것 부터 삭제 됩니다.

 

문제가 생겼을때는 바로 확인할 수 있도록 합니다.