Extended Event DDL and Filter out Temp tables?

Question

Extended Event DDL and Filter out Temp tables?

Lowell

SSC Guru

Points: 323518
More actions
August 22, 2016 at 9:42 am

#316498

I'm trying to create an Extended event session that captures DDL, but ignores temp table Creation;
my script is very simple, but i'm looking for an operator that does not seem to exist:
i want NOT LIKE instead of [sqlserver].[like_i_sql_unicode_string]
any idea how i filter the objectname ?
CREATE EVENT SESSION [DDLChanges] ON SERVER
ADD EVENT sqlserver.database_created(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.object_altered(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.object_created(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([object_name],N'''#%'''))),
ADD EVENT sqlserver.object_deleted(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'DDLChanges.xel',max_file_size=(200))
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
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try filtering on database_id, or is the db_id the id of the connection rather than the table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

GilaMonster (8/22/2016)
Try filtering on database_id, or is the db_id the id of the connection rather than the table?

doh simple and obvious;

the database_id =2 , regardless of what database context that was creating the temp table.

Thank you Gail!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!