/*****************************************************************************
*   FileName:  Demo 2 - SQLskills_TrackTableUsage_2012.sql
*
*   Summary: Demonstrates how to track what statements used a specific table
*            in a database based on object level locks.
*
*   Date: September 7, 2011 
*
*   SQL Server Versions:
*         2012
*         
******************************************************************************
*   Copyright (C) 2011 Jonathan M. Kehayias, SQLskills.com
*   All rights reserved. 
*
*   For more scripts and sample code, check out 
*      http://sqlskills.com/blogs/jonathan
*
*   You may alter this code for your own *non-commercial* purposes. You may
*   republish altered code as long as you include this copyright and give 
*	due credit. 
*
*
*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
*   PARTICULAR PURPOSE. 
*
******************************************************************************/
USE AdventureWorks2012
GO

IF EXISTS(SELECT * 
		  FROM sys.server_event_sessions 
		  WHERE name='SQLskills_TrackTableUsage')
    DROP EVENT SESSION [SQLskills_TrackTableUsage] ON SERVER;
GO
-- Create the Event Session
DECLARE @SQLcmd NVARCHAR(MAX) = N'
CREATE EVENT SESSION [SQLskills_TrackTableUsage]
ON SERVER
ADD EVENT sqlserver.lock_acquired(
	ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
	WHERE (sqlserver.session_id = '+CAST(@@SPID AS NVARCHAR) + ')
	--WHERE (resource_type = 5 --Object Level Lock
	--			OR resource_type = 11
	--			AND sqlserver.is_system = 0
	--		  --AND  resource_0 = '+ CAST(OBJECT_ID('[HumanResources].[Employee]') AS NVARCHAR) + '
	--		  --AND (mode = 6		--IS locks
	--				--OR mode = 7 --IU locks
	--				--OR mode = 8) --IX locks
	--	  )
)
ADD TARGET package0.ring_buffer'
PRINT @SQLCmd
EXECUTE(@SQLCmd)
GO
-- Start the Event Session
ALTER EVENT SESSION [SQLskills_TrackTableUsage]
ON SERVER
STATE=START
GO

--Generate some data
EXECUTE dbo.uspGetBillOfMaterials @StartProductID = 517, @CheckDate = '2004-05-01'
GO
EXECUTE dbo.uspGetEmployeeManagers 260
GO
EXECUTE dbo.uspGetBillOfMaterials @StartProductID = 785, @CheckDate = '2004-10-01'
GO
EXECUTE dbo.uspGetManagerEmployees 3
GO
SELECT TOP 5 *
FROM HumanResources.Employee
GO
UPDATE HumanResources.Employee
SET JobTitle = JobTitle
GO
UPDATE HumanResources.Employee
SET JobTitle = JobTitle
WHERE BusinessEntityID = 1
GO

-- Drop the lock_acquired event from the session
ALTER EVENT SESSION [SQLskills_TrackTableUsage]
ON SERVER
DROP EVENT sqlserver.lock_acquired
GO

-- Using offset parsing to get the executing statement from cache
SELECT
	event_name, 
	timestamp,
	accessed_database_id, 
	accessed_object_id, 
	lock_resource,
	lock_mode, 
	st.objectid as accessing_object_id,
	SUBSTRING(st.text, (tsql_stack.value('(/frame/@offsetStart)[1]', 'int')/2)+1,
	 ((CASE tsql_stack.value('(/frame/@offsetEnd)[1]', 'int')
	 WHEN -1 THEN DATALENGTH(st.text)
	 ELSE tsql_stack.value('(/frame/@offsetEnd)[1]', 'int')
	 END - tsql_stack.value('(/frame/@offsetStart)[1]', 'int'))/2) + 1) AS statement_text_from_cache,
	sql_text,
	st.text as accessing_object_definition
FROM
(
SELECT 
	n.value('(event/@name)[1]', 'varchar(50)') AS event_name, 
	DATEADD(hh, 
		DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
		n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp], 
	n.value('(event/data[@name="database_id"]/value)[1]', 'int') AS [accessed_database_id], 
	n.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [accessed_object_id],
	OBJECT_NAME(n.value('(event/data[@name="object_id"]/value)[1]', 'int')) AS [accessed_object_name],
	n.value('(event/data[@name="mode"]/text)[1]', 'nvarchar(10)') AS [lock_mode], 
	n.value('(event/data[@name="resource_type"]/text)[1]', 'nvarchar(20)') AS [lock_resource], 
	n.query('(event/action[@name="tsql_stack"]/value/frames/frame)[1]') AS [tsql_stack], 
	REPLACE(n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'), CHAR(10), CHAR(13)+CHAR(10)) AS [sql_text]
FROM
(	SELECT td.query('.') as n
	FROM 
	(
		SELECT CAST(target_data AS XML) as target_data
		FROM sys.dm_xe_sessions AS s 
		JOIN sys.dm_xe_session_targets AS t 
			ON t.event_session_address = s.address
		WHERE s.name = 'SQLskills_TrackTableUsage'
		  AND t.target_name = 'ring_buffer'
	) AS sub
	CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) as tab
) as tab2
CROSS APPLY sys.dm_exec_sql_text(tsql_stack.value('xs:hexBinary(substring((/frame/@handle)[1], 3))', 'varbinary(max)')) AS st
