Contents

1 소스

2 참고자료

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

• Finding out who dropped a table using the transaction log