Pfthb

I'm attempting to use the Execution Plan XML to analyse dynamic SQL statements, specifically to determine whether or not it's referencing a non indexed view. The hope is to systematically navigate the XML via XPath within a separate C# project.

However it would seem that by default the SQL statement gets expanded to the constituent tables prior to the QEP being generated, and as such it doesn't include a reference to the view. (I'd like to avoid having to rely upon a string based search of the Statement Text)

Is there an option by which I can expand the XML plan to include the view expansion, or is there an alternative approach I might want to consider?

Thanks.

When the query execution plan is constructed, it goes through three phases - parsing, binding and optimization. In binding phase non indexed views are replaced with their definition and in optimization phase unused columns of the view are removed. So in the final execution plan there is nothing left from the view itself. This isn't true when NOEXPAND hint is specified in the query, which is the only case when you can find the view in the execution plan. But this require changes in the queries, which may not be an option in your case.

If you want to search for views usage in your queries, probably you should try to get the query text returned from sys.dm_exec_sql_text and search there (which isn't trivial though, because VIEW$SOMETHING will be found in the query referencing VIEW$SOMETHING_ELSE).