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());

}
  1. Using a SQL parser, as example:

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

Comments

Popular posts from this blog

SSIS - Script Task error: Exception has been thrown by the target of an invocation.

Don’t install Hadoop on Windows!

SSIS - script component DateTime Formats Implicit Conversion