-1

I need to get a list columns used in select statment, join, where and order by in query from 'show execution plan xml' in SQL Server into a table.

Eg query:

Select a.id, a.name, a.gender, a.marks, b.address
From #temp a
Inner join #temp1 b
On a.id=b.id
Where id=1

Output should be:

Select- id, name, gender, marks(from #temp) 
Address(from #temp1) 
Join - id
Where- id

All together into a table.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Userabc
  • 11
  • 2
  • 1
    Because an Microsoft SQL Server plan is in a XML format, you can have all that you want by parsing the XML with nodes, query and value XML methods – SQLpro Mar 05 '21 at 08:53
  • You do realize that it's next to impossible to get a definitive result. For example, what of the optimizer chooses a filtered index or an indexed view to satisfy the query? What if the join is a correlated nested loops join where the join condition is a predicate at the inner seek? What about computed columns? Commutated joins, elided joins, unions, merge intervals? It's an absolute minefield – Charlieface Mar 05 '21 at 10:35

1 Answers1

0

As an example of code :

DECLARE @PLAN_XML XML;

--> put here the query you wanted to examine the plan
SELECT DISTINCT name, object_id, create_date 
FROM sys.objects;

SELECT @PLAN_XML = query_plan 
FROM   sys.dm_exec_requests 
       OUTER APPLY sys.dm_exec_query_plan(plan_handle) 
WHERE session_id = @@SPID;

WITH   XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' )
SELECT CONCAT(x.p.value('(@Schema)', 'sysname'), N'.', 
              x.p.value('(@Table)', 'sysname'), N'.', 
              x.p.value('(@Column)', 'sysname')) AS OUTPUT_COLUMN
FROM   @PLAN_XML.nodes( 'ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[1]/QueryPlan/RelOp/OutputList/*' ) x(p)

This will give you the result :

enter image description here

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • In the same how to get columns used to join and where clause as a column???? – Userabc Mar 05 '21 at 10:21
  • As I say in my comment above, this is a brilliant example of why it's impossible. `sys.objects` was queried, not `sys.sysschobjs`, and there is all manner of filter and union predicates going on underneath – Charlieface Mar 05 '21 at 10:37
  • It is only because it is a view... – SQLpro Sep 12 '22 at 13:23