SQL Server Identifying Connections that are utilising SQL from an application
So you want to differentiate between the app calling stored procedures vs sending T-SQL directly? Sounds like an Extended Event trace would so. Assuming the programmers call these procedures correctly from the API (and not just send a text string with EXEC procname in it).
If you capture sqlserver.sql_batch_completed and sqlserver.rpc_completed I think you will cover the submissions into SQL Server.
When an app doesn't call a proc you should either see sql_batch_completed or rpc_completed. For the later, you can check out the object_name column and if it is sp_executesql, then you have the &quot;not calling stored procedure&quot; case.
When it does call a proc, you will have (hopefully, if they do it right) rpc_completed with something else in the object_name column.
Depending on how sophisticated you want to be, you can play around with event_counter and the histogram targets. Or even store the trace data in a table and query that table.