0

I have set up an extended event to capture "sp_statement_completed" events, the script to create it is below.

CREATE EVENT SESSION [t1] ON SERVER ADD EVENT sqlserver.sp_statement_completedADD TARGET package0.event_file(SET filename=N'D:\DB_A\1.xel',max_file_size=(0),max_rollover_files=(0))WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

I need to capture the schema name part of the every event.

Please guide me.

Manikandan Ponnusamy
  • 79
  • 1
  • 6
  • Hi, I am not sure I understand: "schema name part" of the every event? Do you mean as part of the object name? as in dbo.ObjectName? – MojoDBA Mar 07 '19 at 10:05
  • Object Name contains only Store procedure name. it don't have schema name. – Manikandan Ponnusamy Mar 07 '19 at 10:57
  • Interesting. You'd think it would be provided by the event/s. But no. ObjectID is there, though. I'd use the XE sessions XML output, pull the object_id field (something like this: `xed.event_data.value('(data[@name="object__id"]/value)[1]', 'int') AS [ObjectID])` and join to `sys.all_objects` and `sys.schemas` Or use RPC/Batch started events and parse it out from the SQL_Text. – MojoDBA Mar 07 '19 at 12:31

1 Answers1

0

Try this:

DECLARE @Target_Data XML =
(
SELECT TOP 1 Cast(xet.target_data AS XML) AS targetdata
FROM sys.dm_xe_session_targets AS xet
INNER JOIN sys.dm_xe_sessions AS xes ON xes.address = xet.event_session_address
WHERE xes.name = 't1'
AND xet.target_name = 'ring_buffer'
);

SELECT
  xed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type,
xed.event_data.value('(data[@name="object_id"]/value)[1]', 'int') AS [object_id]
INTO #HoldXEData
FROM @Target_Data.nodes('//RingBufferTarget/event') AS xed (event_data)

SELECT XE.*, O.name AS Object_name, S.name AS Schema_name
FROM #HoldXEData AS XE
INNER JOIN sys.all_objects AS O ON XE.object_id = O.object_id
INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id

DROP TABLE #HoldXEData
MojoDBA
  • 118
  • 7