Transact-SQL parsing

In this document, you'll learn how Dataedo uses an SQL parser with the Transact-SQL dialect and explore its capabilities.

Supported SQL engines

The following SQL engines use this dialect:

• SQL Server
• Azure SQL Database
• Azure Synapse Analytics (formerly SQL DW)
• Azure SQL Edge

Import schema from DDL

Data Definition Language (DDL) is a subset of SQL used to define and manage the structure and relationships within a database. Using an SQL parser, Dataedo imports the schema from the provided DDL to map out the database structure.

If the script creates views, the columns of those views will not have data types assigned.

Automatic column-level lineage from SELECT||CREATE VIEW statements

Dataedo will create column-level data lineage for views and SQL queries based on the script. Just leave the Automatic lineage option selected during import.

If you deactivate this feature during import, you can still create column-level lineage automatically using the Parse Script to Lineage functionality in the Script tab.

This function also applies to manually added views created with a script, as well as SQL query objects.

Supported syntax overview

Supported SQL Syntax

Dataedo supports automatic lineage creation for the following Transact-SQL syntax:

• SELECT and CREATE VIEW statements
• CASEs and other logic-related expressions
• UNIONs
• JOINs
• Aliases
• Functions and operators
• Subqueries and nested queries
• Non-recursive Common Table Expressions (CTEs)
• Linked servers with single database

Unsupported SQL Syntax

Dataedo does not support automatic lineage creation for the following Transact-SQL syntax:

• Statements that access nested data such as JSON, XML, or objects as columns
• PIVOT and UNPIVOT relational operators
• Recursive Common Table Expressions (Recursive CTE)
• Dynamic queries
• Window functions
• Linked servers with multiple databases
• Synonyms

Automatic column-level lineage from imported Stored Procedures and User Defined Functions

Dataedo generates column-level data lineage for stored procedures and user-defined functions based on their scripts. To use this feature, make sure you selected the Automatic Lineage option during the initial import or when modifying import settings.

Dataedo divides procedures into steps, representing each step as a process and creating data lineage for supported steps. Unsupported steps are labeled using the first word of the process script, followed by ellipses (...).

In the Script tab, each process includes a fragment of the procedure script it corresponds to.

Supported syntax overview

Supported SQL Syntax

Dataedo supports automatic lineage creation for the following Transact-SQL syntax:

• INSERT
• UPDATE
• MERGE
• SELECT INTO
• CREATE TABLE AS SELECT
• OUTPUT/RETURN statements
• Non-recursive Common Table Expressions (CTEs, WITH statements)

When temporary tables are created in a procedure (i.e., tables that do not normally exist in the documented database), Dataedo will attempt to merge the data lineage entering and exiting these tables. Instead of representing the lineage as SOURCE_TABLE -> #TEMP_TABLE -> DESTINATION_TABLE, it will simplify this to SOURCE_TABLE -> DESTINATION_TABLE.

However, due to the limitations of ANTLR, the presence of unsupported elements in a procedure may prevent it from being parsed entirely. At this stage, any procedure containing elements outside those explicitly supported is considered unsupported. Nevertheless, we strive to ensure that unsupported elements do not interfere with the processing of supported steps.

Unsupported SQL Syntax

Dataedo does not support automatic lineage creation for the following Transact-SQL syntax:

• DELETE statements
• BULK INSERT statements
• COPY INTO statements
• Syntax mentioned as unsupported in the section above

Online SQL Parser

Check out Dataedo's online SQL parsing and analysis tool to test SQL parsing capabilities in real time.