sp_statement_completed & sql_statement_completed and look for sp_executesql or EXEC with brackets.
Or the entire batch with sql_batch_completed and RPC completed. Eitherway, a second filter is necessary, and probably better to do it after capturing, especially for the EXEC( one
That would work for SQL generated on the SQL server, but isn't going to help with dynamic SQL generated on an application. From experience, I've found applications are far worse for it, it seems that (some) application/web developers don't even consider; and then as a DBA you see the application code and have a melt down. :'(
Unfortunately, I can't really think of a way you could capture dynamically generated SQL from an application. It is, one of the reasons why I prefer only letting an application use SPs; as then it can't even try to use dynamically generated SQL.
No. That will likely come through from the app as a sql_batch_completed event (parameterised stuff tends to show up as rpc_completed with a call to sp_executesql, at lease ORMs show up that way), so you should pay attention specifically to that event when it's not from SSMS.
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