Skip to main content
SimilarExport ZIP
100%

Some examples with Microsoft.SqlServer.TransactSql.ScriptDom and TSql generation and parsing

✓ Published0🌍 Public
HHugoberry
Last edited Mar 22, 2016
Created on Jul 7, 2017

This example demonstrates how to parse and inspect T-SQL scripts using Microsoft.SqlServer.TransactSql.ScriptDom, a parser for SQL Server’s dialect. It shows how to traverse the abstract syntax tree with a custom visitor class that overrides `ExplicitVisit` for `SelectStatement` nodes, extracting table names, schema, database, and alias information from the `FromClause`. The code also regenerates SQL from the parsed fragment using `Sql120ScriptGenerator`. The example uses the `TSql120Parser` and `TSqlFragmentVisitor` APIs, processing multiple statements in a single input string to illustrate the visitor pattern for query analysis.

AI-generated description

Similar vizzes

Loading thumbnail…

Use `sqlfluff` to build a lineage tree in D2

This visualization parses T-SQL view definitions from `vwMarket.sql` and renders a lineage tree showing table and column dependencies, using `sqlfluff` to generate a parse tree and `D2` to draw the diagram. The code extracts aliases from `from_clause` nodes to list source tables as SQL-table shapes, and pulls column-to-column mappings from `expression` segments where comparisons use the `=` operator, outputting edges like `alias.column -> other.column`. It relies on `sqlfluff.parse` for T-SQL grammar analysis and recursive JSON traversal via `get_json_segment` to locate `from_expression_element` and `column_reference` structures, printing D2 syntax directly to standard output. The example demonstrates how to bridge SQL parsing with declarative diagramming for dependency visualization.

HHugoberry
57% match
Loading thumbnail…

XSLT for transforming an XML doc into DDl statements for schema population

This example demonstrates an XSLT 1.0 stylesheet that transforms an XML DataSet schema into SQL DDL statements for creating database tables. It shows how to generate `CREATE TABLE` commands with identity columns, foreign key constraints, and primary key handling from XSD-defined complex types. The transformation uses XSLT templates, variables, and the `document()` function to process included schemas, while the `xsl:output method="text"` produces plain SQL text. The stylesheet maps XML schema types to SQL data types via a custom `Types` template, iterating over field elements and attributes to build column definitions.

HHugoberry
52% match
Loading thumbnail…

Gist 0ca6276fe1534c7941d45e4bb229cc41

This example demonstrates a SQL Server stored procedure that parses incoming JSON data and inserts it into a relational table. It shows how to use the `OPENJSON` function with an explicit schema definition to extract key-value pairs, mapping the JSON’s `Key` and `Value` fields into corresponding integer and varchar columns. The procedure accepts an `nvarchar(max)` parameter for the JSON payload, making it reusable for dynamic data ingestion. The visualization highlights the transformation from a semi-structured JSON format into a normalized table structure, emphasizing the schema-mapping capabilities of T-SQL’s JSON support.

HHugoberry
50% match
Loading thumbnail…

Gist faec7eb6c03589ff608bab991699407b

This example demonstrates how to generate and execute a dynamic SQL script in Power Query (M language) to bulk-insert data into a SQL Server table using a table-valued parameter (TVP). It builds a textual TVP declaration and populates it by chunking source rows into sets of 1,000 entries, then constructs a multi-statement script that inserts each chunk and calls a stored procedure. The code relies on M functions like `Table.TransformRows`, `List.Generate`, `Text.Combine`, and `Value.NativeQuery` to programmatically assemble and run the query. The visualization highlights the data flow from a source table to a SQL stored procedure, emphasizing the chunking logic and the use of native query execution.

HHugoberry
50% match
Loading thumbnail…

Gist 8e3363becb45788f796a2b31aaa4c6b3

This example shows how to programmatically generate Markdown documentation from Power Query’s metadata system, specifically pulling the name, description, category, and examples for the `SqlExpression.SchemaFrom` type. It works by reading the type’s metadata via `Value.Metadata` and `Value.Type`, then formatting the results into a structured document with headers, code blocks, and separators. The code uses the `Lines.ToText` and `List.Transform` functions to assemble the output, while `Documentation.Examples` iterates over sample entries to embed their code and results. The final output is a plain-text Markdown string, ready for use in a gallery or README.

HHugoberry
50% match
Loading thumbnail…

Gist 8ddacfa41ce888f458863185ecfc400f

This example demonstrates a stored procedure that converts XML data into relational rows for insertion into a database table. The code defines `usp_InsertDemoXML`, which accepts a `varbinary(max)` parameter, casts it to XML, and then uses XQuery’s `nodes()` and `value()` methods to extract `key` and `value` pairs from each `<row>` element. It shows a data-ingestion pattern for parsing structured XML payloads into a normalized key-value table structure. The procedure relies on T-SQL’s built-in XML support rather than external visualization libraries, focusing on server-side data transformation.

HHugoberry
49% match
Loading thumbnail…

Gist 4905f6e091305c6a3ff3c1fe2c0a50b2

This example visualizes the structure of a DAX query plan by parsing a text-based log file into a hierarchical tree. It uses the Moo lexer to tokenize lines, identifiers, column references, and operator types, then builds a nested representation of the plan. The visualization likely renders this tree to show the flow of logical and physical operations, with each node displaying its operator type and associated properties such as filters, joins, or spooling details. The code defines custom token patterns for DAX-specific syntax, including column references like `'Table'[Column]` and measure references like `[Measure]`, and parses them into structured objects for display.

HHugoberry
48% match
Loading thumbnail…

[PowerBI/PowerQuery/PowerShell] Retrieve the documentation that has been nicely packaged by dox query into the tabular Model

This example retrieves documentation from Power BI’s embedded tabular model and writes it into markdown files organized by module and subcategory. It shows how to extract metadata from the model’s internal `dox` table using an XMLA query, then sorts and groups the results by module and name. The script uses PowerShell with the `Invoke-ASCmd` cmdlet from the SQLServer module, reading the localhost port from `msmdsrv.port.txt`, and writes each documentation row’s `doc` value to a `.md` file, escaping `{{` and `}}` for template compatibility. The output folder structure mirrors the module and subcategory hierarchy, with folders created on demand.

HHugoberry
47% match