Expose SQL Parse Tree - by Bret Stateham

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


4
2
Sign in
to vote
ID 709658 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 11/29/2011 3:52:51 PM
Access Restriction Public

Description

Currently there is no visibility into the internal tree created by the SQL parser. I would be helpful if we could retrieve the tree for the purpose of programmatically determining the objects that a statement directly depends upon.  Currently, the developer has to resort to their own parsing methods to extract the object references from the query.  
Sign in to post a comment.
Posted by Bret Stateham on 12/6/2011 at 4:34 PM
Umachandar,

Sorry I didn't respond yesterday, and it looks like you have closed this already, but I'll leave a comment anyhow. First, my apologies for the incorrect use of the term "parse tree". Sounds like the binding information is more appropriate.

The methods that you mention don't do exactly what I'm looking for.

If I have a string variable in a client application that contains a SQL statement or batch (or or more SELECT, INSERT, UPDATE, DELETE, EXEC, etc...), I would like to be able to determine the objects that are:

1) directly referenced in the statement/batch itself
2) indirectly referenced (the entire dependency tree)

The SHOWPLAN and sys.dm_sql_referenc* methods you mentioned don't do that.

SHOWPLAN does NOT show direct dependencies. If I have a view, V1, that depends to tables T1 and T2 and I look at the SHOWPLAN for a SELECT * FROM V1, the T1 and T2 table names will be there but NOT the V1 view that is directly referenced by the SHOWPLAN.

The sys.dm_sql_referenced_entities & sys.dm_sql_referencing_entities objects require that I have an existing object already in the database from which I would like to extract the dependencies. I don't have an existing object. I have an adhoc statement in a client side string that I would like to pass as an argument to a system stored procedure or function.

The new SQL 2012 sys.dm_exec_describe_first_result_set & sys.dm_exec_describe_first_result_set_for_object objects look promising, I'll have to try them out. My guess is that they will have the same shortcomings as showplan in that they will show the base object dependencies and not the direct dependencies (views, table valued functions, scalar functions), etc.

sp_describe_first_result_set with the @browse_information_mode parameter looks like it might fit the bill.

My current approach requires that I personally parse the SQL Statements my self to extract the object names referenced by the statement. Since SQL Server has to do that anyhow, it would be helpful if we had a function we could call to extract the information generated by SQL Server's own parser rather than having to invent our my T-SQL language parser.
Posted by Umachandar [MSFT] on 12/5/2011 at 12:47 PM
Hello Bret,
Thanks for your feedback. But it is unclear what you mean by parse tree. We use the term parse tree in the team but it means different things that what you envisioned. For example, the parse tree doesn't contain the binding information - resolution of identifiers to objects.
In any case, SQL Server has many mechanisms to obtain the metadata depending on what you want:

1. SHOWPLAN - This can provide tree for statements
2. sys.dm_sql_referenced_entities & sys.dm_sql_referencing_entities - These SQL dependency DMVs can also be used to discover metadata about modules / objects if that is what you want
3. Metadata Discovery Features in SQL Server 2012 - sys.dm_exec_describe_first_result_set & sys.dm_exec_describe_first_result_set_for_object and others can be used to discover metadata too

Lastly, we also have the Microsoft.SqlServer.TransactSql.ScriptDom managed API in SQL Server 2012 that can be used to obtain the syntax tree. You can use it to parse any T-SQL statement and obtain an object representation. See MSDN for more details on the API. This is a newer version of the ScriptDom API that was introduced by VS 2008 Database Edition.
Hope this helps. Otherwise, we do not have any plans to add such API in the engine itself so you need to look at the ScriptDom or one of the engine features described above to see if it will work for you.

--
Umachandar, SQL Programmability Team