Tech Tip: How to Parse an SQL Command?

Photo by Caspar Camille Rubin on Unsplash

It is not easy to extract object names from an SQL command since they may be written in different ways (with/without schema, databases name included …). Still, there are many option to extract objects from an SQL query that you can try.

This quick tip illustrates different approaches to parse a SQL Command.

Regular Expressions

Regular expressions are patterns used to match character combinations in strings. They can be used to extract the different parts composing a SQL command. For example: You have to search for the words located after the following keywords:

• TRUNCATE TABLE
• FROM
• UPDATE
• JOIN

The following code is a C# example:

Regex regex = new Regex(@"\bJOIN\s+(?<Retrieve>[a-zA-Z\._\d\[\]]+)\b|\bFROM\s+(?<Retrieve>[a-zA-Z\._\d\[\]]+)\b|\bUPDATE\s+(?<Update>[a-zA-Z\._\d]+)\b|\bINSERT\s+(?:\bINTO\b)?\s+(?<Insert>[a-zA-Z\._\d]+)\b|\bTRUNCATE\s+TABLE\s+(?<Delete>[a-zA-Z\._\d]+)\b|\bDELETE\s+(?:\bFROM\b)?\s+(?<Delete>[a-zA-Z\._\d]+)\b");

var obj = regex.Matches(sql);

foreach(Match m in obj)
{

    Console.WriteLine(m.ToString().Substring(m.ToString().IndexOf(" ")).Trim());

}

SQL parsers

There are several open-source projects and third-party tools for parsing SQL commands.

.NET Tsql Parser

This is an official .NET library that accurately produces an Abstract Syntax Tree (AST) representation of the T-SQL code.

Programmatically parsing Transact SQL (T-SQL) with the ScriptDom parser - Azure SQL Devs' Corner

SQLParser.com

A commercial SQL parser developed by GUDU software company.

SQL Parse, Analyze, Transform and Format All In One

GitHub SQL Parsers

There are several SQL parsers published on GitHub. They are listed under the sql-parser topic.

Build software better, together

Other projects

Other open-source projects can be found on different public repositories such as Code Project.

SQL Parser