I’m working on a script and wanted to know how SQL was caching the query plan. So I wrote this little snippet. The only real value I’m adding here is getting the XQuery correct to pull out the SQL itself.
DBCC FREEPROCCACHE; -- Run your query here SELECT qp.query_plan, qp.query_plan.value(N'(//@StatementText)', N'NVARCHAR(MAX)') FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp;
Of course, I don’t recommend that DBCC in a production environment. But in a dev environment, it makes things easy.