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

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

  • Hi

    very nice. Many thanks for the explanations.

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


    a missing block:

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


    Frank Kress

  • 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.

  • 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...

  • 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.

  • 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...

  • 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]







    SELECT *

    FROM sometab2;

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

  • 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()).

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

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