Extended Events and Views

pgwarwick

Hi I am monitoring a server for when a particular table is accessed I can trap ad-hoc TSQL and stored procedures that contain the particular table I am interested in but not Views I can see when a view is run but am unable to see the contents of a view Does  anyone know which event and property I need to set up Many thanks

GrantF

There isn't an easy to monitor just a table. Now, you could capture sql_batch_complete events and do a search inside the query text for your table name as a filter. However, it's going to be a somewhat poor filter and might put some load on the server. You'd want to experiment with this carefully. It won't show you any views accessing the table, because, as you say, SELECT * FROM MyView, doesn't show which tables are in the view. To capture views, you'd need to add an additional filter, again on the query, and look for the views, that you can look up on your own, that MIGHT access the table (the optimizer can eliminate tables from a view through part of the optimization process called simplification, where it determines if all tables listed in the query are needed in order to satisfy it). You would also need to walk through your stored procedures and identify which of those is hitting the table in question. Those are easy to filter since rpc_completed, the event you need, lists the object name. Simplest part of the equation here.

I'm not sure how you'd do prepared statements though, if you have those. That would take some experimentation.

But yeah, as you can see from all this, trying to monitor access to a table is very difficult. Why do you need to do that? Is there another way to satisfy the business need?