Tech Tip: How to Parse an SQL Command?
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 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
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)
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
Arvind Shyamsundar .NET Developers are perhaps familiar with libraries like Roslyn and CodeDOM, which allow in-depth…
A commercial SQL parser developed by GUDU software company.
SQL Parse, Analyze, Transform and Format All In One
Validates syntax without connecting to a database Prepares a detailed SQL parse tree node structure Highlights syntax…
GitHub SQL Parsers
There are several SQL parsers published on GitHub. They are listed under the sql-parser topic.
Build software better, together
GitHub is where people build software. More than 100 million people use GitHub to discover, fork, and contribute to…
Other open-source projects can be found on different public repositories such as Code Project.