Parsing SQL queries in MS SQL Server

September 4, 2023

This is the third article in our series on parsing SQL in different database technologies and SQL dialects. We explored SQL parsing on Snowflake and then Oracle in our earlier blog posts. We cover SQL parsing on MS SQL Server in this blog post.

We provide practical examples of interpreting SQL from the SQL Server query history. Additionally, we will present some code that utilizes the FlowHigh SQL parser SDK to programmatically parse MS SQL. The parsing of SQL Server SQL can be automated using the SDK.

In another post on the Sonra blog, I go into great depth on the benefits of using an SQL parser.

One example for a use case would be table and column audit logging. Audit logging refers to the detailed recording of access and operations performed on specific tables and columns in a database including execution of distinct in SQL queries. Such logging can be essential for ensuring security, compliance with regulatory standards, and understanding data access patterns.

SQL parser for SQL Server

A powerful online SQL parser designed for any SQL dialect, including MS SQL Server, is called FlowHigh. This SaaS platform includes an easy-to-use UI for manual SQL parsing as well as an SDK for managing bulk SQL parsing requirements or automating the operation. We demonstrate FlowHigh’s capabilities by parsing the query history of the MS SQL Server. To programmatically parse the query history, we used the SDK.

Programmatically parsing the MS SQL query history with the FlowHigh SDK

MS SQL Server keeps an extensive log of all SQL statements cached in its plan cache, which can be examined for performance data and query specifics. The dynamic management views of SQL Server can be used to get this data.

sys.dm_exec_query_stats and the associated function sys.dm_exec_sql_text(), is a crucial part of the SQL analysis process. The sys.dm_exec_query_stats view provides information on the execution statistics of cached query plans, including information on the number of executions, CPU usage, total time spent, and more. You can get the precise text of the SQL statement connected to each cached plan by using the sys.dm_exec_sql_text() method in combination with it. This is very helpful for locating and analysing executed SQL statements that are affecting the speed of the SQL Server instance.

These dynamic management items work as useful resources for understanding SQL execution characteristics and patterns in MS SQL Server.

To get the required data from the MS SQL query history, we used the following SQL query:

The Python code provided below was then used to parse all of the questions that were pulled from the query history:

Analysing the output of the FlowHigh SQL parser

An SQL query is ingested by the FlowHigh SQL parser for MS SQL Server, which then returns the processed result either as a JSON or XML message. For instance, the parser produces a full JSON message of the SQL query from sys.dm_exec_query_stats. This output includes information on the filter conditions, fields fetched, aliases used, join conditions, tables and other components of the SQL statement.

Let’s go through an example

In addition to being thorough, the output XML is also more condensed than JSON. For illustration, the following is an example SELECT statement output in XML:

A complete description of the SQL statement, including the fully qualified table name, any where conditions, and joins used in the query, are provided in the XML from Flowhigh SQL parser. When looking into our sample query, FlowHigh identifies attributes such as ProductID, ProductName, CategoryName, and SubcategoryName sourced from three tables.

You will also find the two inner joins along with the columns in the join condition.

With the help of FlowHigh’s SQL parser and the corresponding XSD schema, we can analyse, comprehend, and modify SQL statements taken from the query history of MS SQL Server. We gain insights into database relationships and structures by dissecting the complex properties and patterns hidden within the SQL queries through analysis of the parsed data.

The FlowHigh SDK ships with various abstractions to easily identify the following information:

Columns in join conditions

Columns used in GROUP BY / ORDER BY clause

Filter columns

Table types such as persisted tables, inline views, CTEs, views etc.. This also includes columns in the SELECT clause

Do you think we are missing abstractions? Contact us with your suggestions

Gain access to FlowHigh today.

Get Access