#title 트랜잭션 로그에서 DROP된 테이블 찾는 방법
[[TableOfContents]]

==== 소스 ====
{{{
WITH x
AS
(
	SELECT 
		CONVERT(xml, 
			'<r>' + 
			REPLACE(
				REPLACE([Lock Information], 'ACQUIRE_LOCK_SCH_M OBJECT: ', '')
				, ':', '</r><r>'
			) +
			'</r>'
		) xitems
	FROM ::fn_dblog (NULL, NULL)
	WHERE [Lock Information] LIKE '%SCH_M OBJECT%'
	AND [Transaction Id] IN (
		SELECT [Transaction Id]
		FROM ::fn_dblog (NULL, NULL)
		WHERE [Transaction Name] = 'DROPOBJ'
	)
)
SELECT
	xitems.value('/r[1]', 'int') [DB ID]
,	xitems.value('/r[2]', 'int') [Object ID]
FROM x
}}}

==== 참고자료 ====
 * [http://www.sqlskills.com/BLOGS/PAUL/post.aspx?id=c0facc5c-d236-479c-b735-cf89ce8a2ab7 Finding out who dropped a table using the transaction log]