Stairway to ScriptDOM Level 2 - Parsing, Tokens, and the Abstract Syntax Tree

Question

Post reply

Stairway to ScriptDOM Level 2 - Parsing, Tokens, and the Abstract Syntax Tree

Diligentdba 46159

SSCommitted

Points: 1544
More actions
April 13, 2022 at 12:00 am

#4011179

Comments posted to this topic are about the item Stairway to ScriptDOM Level 2 - Parsing, Tokens, and the Abstract Syntax Tree

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

frank.kress SSC Veteran Points: 281 More actions · Answer 1

Hi

very nice. Many thanks for the explanations.

One question: how can something be added later in the procedure?

Example:

a missing block:

BEGIN
  Insert Into Table_2(Field_1,Field_2,Field_3)
  Select field_1,field_2,field_3
  from table_1
END

regards

Frank Kress

Diligentdba 46159 SSCommitted Points: 1544 More actions · Answer 2

Hi Frank , yes you can add stuff and I will be explaining that in later examples but it may be a little more complex that we think.

Thank you for reading.

corey lawson Hall of Fame Points: 3773 More actions · Answer 3

Some things in Azure Synapse don't exist or work the same compared to "regular" TSQL. I usually find at some point I need to find out what distinct objects (tables, views, functions) a given view, proc or function might need. In Synapse, sys.dependencies only works for views and the tables or views they use.

So that leaves doing something with the code in the source code system tables/views...

It's easy enough nowdays to "split" each object's source code text blob in sys.sql_modules and do some simple pattern-matching on those. But since TSQL (still) doesn't have real regular expressions available out-of-the-box, doing much more with TSQL string functions gets onerous quickly, and any kind of weird code formatting will be painful to work with (one-liner queries or joins, say).

So this series looks to be quite useful to me in my world, and available to help with other things that may contain TSQL queries or code that might need to have this info extracted, whether said code is in a database or external, like within source control files, or embedded SQL in SSIS packages or SSRS reports. Huzzah for MS for making it, and those who've figured out how to use it. Now if only there were similar .Net DLLs for Oracle PL/SQL, and so on...

Diligentdba 46159 SSCommitted Points: 1544 More actions · Answer 4

Am very glad to hear that it is useful! Thanks for the comment. Yeah this is just a t-sql library and one that has been around a very long time. Am not sure we will get similar for other SQL languages..maybe if more people ask for it it may happen.

corey lawson Hall of Fame Points: 3773 More actions · Answer 5

well in working with other stuff out there regarding using scriptdom.dll, at the very least, the "lexer" output in ScriptTokenStream gets generated regardless of if the SQL was successfully parsed. But getting more from the parser (.ParseStatementList()) only has actionable output if the input statement got parsed w/o error. So at least for the TSql150Parser, it doesn't want to parse "CTAS" statements from Synapse, for example.

The output from ScriptTokenStream would be useful to help build my own parser, though, at the very least...

Diligentdba 46159 SSCommitted Points: 1544 More actions · Answer 6

Hi Corey, I checked with MSFT on your question on synapse - below is their answer:

CTAS is supported as long as you use recent versions, and in some cases you might need to initialize the parser with the optional SqlEngineType.All enum. For example, the below parses just fine with TSql150Parser(true, SqlEngineType.All) or TSql160Parser(true, SqlEngineType.All)

CREATE TABLE [sometab]

WITH

(

DISTRIBUTION = ROUND_ROBIN

,CLUSTERED COLUMNSTORE INDEX

)

AS

SELECT *

FROM sometab2;

I will try this personally too sometime this week and update here, but looks like setting the enginetype may help.

corey lawson Hall of Fame Points: 3773 More actions · Answer 7

sweet, thanks!

(my quick path to getting new ScriptDom.dll on my mostly locked down work computer is to install, locally, dbatools and "sqlserver" powershell modules.

This one: X\WindowsPowerShell\Modules\SqlServer\21.1.18256\Microsoft.SqlServer.TransactSql.ScriptDom.dll

...has TsqlParser160 in it.

Now if only the .Parse() method would consume almost-valid T-SQL beyond just getting the list of tokens (good or bad) to be able to use most of the Visitor methods.

("almost-valid" in this case means SparkSQL statements, which are mostly the same as T-SQL, but a lot of idiomatic function name differences, such as last_day() instead of eomonth()).