Is it possible to "bind" sp_executesql to the calling procedure, as recorded in the Query Store?
I think that embedding comments is currently the best way to distinguish different pieces of dynamic SQL when viewed from Query Store / Extended events / Trace - unfortunately.
I've had plenty of runtime errors with dynamic sql &amp; temp tables so I don't think it does ENSURE temp table validity &amp; stability.
You can use @@nestlevel to see how many SPs / functions are currently in the call stack, but I think you want more information than that. You can use error_procedure() - but only in a catch block, sounds like what you want is Microsoft to add a function current_procedure() or calling_procedure() that will tell you the name of either the current routine or the routine that called this one - maybe add a request here ? :- https://feedback.azure.com/forums/908035-sql-server