C# - Extract objects from SQL Command

Problem:

How can I extract all objects mentioned in a SQL Command using C#?

Solution:

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 ...)

But there are many option to extract objects from an SQL query that you can try:

1. Using Regular expressions, As 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());

}

2. Using a SQL parser, as example:
   • SQL Parser
   • SQL Parser - Code Project

---

You can refer to the following very helpful links for additional information:

• Regular expression to find all table names in a query
• Parsing SQL code in C#