How to query plan cache for all query's using a table variable?
As far as I can tell, a real table can still be called [@sometable] the same as a table variable, but this is still a useful filter. The definitive XQuery filter seems to be that there is no @Database attribute on a node named Object.
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select qp.query_plan
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_query_plan(p.plan_handle) qp
where qp.query_plan.exist('//Object[empty(@Database) and substring(@Table[1],1,2)=&quot;[@&quot;]')