Get used tables from sql query

Question

I need extract from simple string that represent an sql query the tables that are used on the query without execute the query itself in C#.

Example:

string strQuery = "SELECT * FROM table1 LEFT JOIN (SELECT * FROM table2) tt WHERE tt.name IN (SELECT name FROM table3)";
ArrayList arrUsedTables = GetUsedTablesFromQuery(strQuery);

and after this line the object arrUsedTables would contain: table1,table2,table3

Remember that the query may be much complicated!

I would suggest to use regex, however.... http://stackoverflow.com/a/281059/284240 — Tim Schmelter, Jun 04 '13 at 20:48
You need a SQL parser for .Net: http://stackoverflow.com/questions/76083/parsing-sql-in-net — Josh C., Jun 04 '13 at 20:49
@Andreas That link is a closed question with dead links and for Java — Aaron McIver, Jun 04 '13 at 20:50
@AaronMcIver http://www.antlr4.org is cross platform, the question was asked for Java but the provided solution is valid for C# as well. I did not check the link, sorry for that from my side. — Andreas, Jun 04 '13 at 21:00
If you are using SQL Server, you could execute the query but with FMTONLY option, that would only parse the SQL and return the column information. No rows will be affected. — Jurica Smircic, Jun 04 '13 at 21:02
@jure -- think that will only return the structure of the resultset? You can ask sql server to deliver an "Estimated Query Plan" which will contain all the base tables (I think) -- however I have no idea if it's possible to ask for that via ado.net or linq — Transact Charlie, Jun 04 '13 at 22:19

score 4 · Answer 1 · answered Jun 04 '13 at 22:11

Without going to the DB you can't know for certain the names of the tables used in the query.

What if your query uses a view or a stored procedure?

Without consulting the database, these are transparent to the consumer.

The only way to be certain is to query the list of the tables from the database and then to attempt to parse them from your inline sql.

score 1 · Answer 2 · edited Sep 10 '14 at 00:37

1

You will have to add references and directives for the following assemblies:

using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;
using System.IO;

Then, you may create the GetUsedTablesFromQuery method:

private static ArrayList GetUsedTablesFromQuery(string strQuery)
{
    var parser = new TSql100Parser(true);
    IList<ParseError> errors = new List<ParseError>();
    using (TextReader r = new StringReader(strQuery))
    {
        var result = parser.GetTokenStream(r, out errors);
        var tables = result
            .Select((i, index) => (i.TokenType == TSqlTokenType.From) ? result[index + 2].Text : null)
            .Where(i => i != null)
            .ToArray();

        return new ArrayList(tables);
    }
}

edited Sep 10 '14 at 00:37

Trisped

5,705
2
45
58

answered Jun 04 '13 at 22:22

Alex Filipovici

31,789
6
54
78

Microsoft.Data.Schema.ScriptDom has been replaced with [Microsoft.SqlServer.TransactSql.ScriptDom](http://stackoverflow.com/questions/17013135/microsoft-data-schema-scriptdom-with-vs2012-ultimate). I found the dll at `C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll`. (You do not need the .Sql namespace when using the new namespace/dll. – Trisped Sep 10 '14 at 00:22
So I tried to get the example to work and ran into a number of problems so I came up with my own version which I cannot post here since they locked it (not really a dup since this one requires C# but whatever). You can find my solution at http://stackoverflow.com/a/25778275/641833 – Trisped Sep 11 '14 at 02:49

score 0 · Answer 3 · edited May 23 '17 at 12:28

You can certainly use a SQL parser such as ANTLR, as described in this question and answer, in order to get a full parse of the SQL, and then extract the table names.

Another option is to execute some raw SQL to get the execution plan of the query (using the instructions here). The execution plan is in XML, and then you can use Linq to XML to query the plan for all Table attributes on any ColumnReference tag.

Get used tables from sql query

3 Answers3

Linked