Getting table schema from a query

As per MSDN, SqlDataReader.GetSchemaTable returns column metadata for the query executed. I am wondering is there a similar method that will give table metadata for the given query? I mean what tables are involved and what aliases it has got.

In my application, I get the query and I need to append the where clause programically. Using GetSchemaTable(), I can get the column metadata and the table it belongs to. But even though table has aliases, it still return the real table name. Is there a way to get the aliase name for that table?

Following code shows getting the column metadata.

const string connectionString = "your_connection_string";
string sql = "select c.id as s,c.firstname from contact as c";

using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand command = new SqlCommand(sql, connection))
{
    connection.Open();
    SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
    DataTable schema = reader.GetSchemaTable();
    foreach (DataRow row in schema.Rows)
    {
        foreach (DataColumn column in schema.Columns)
        {
            Console.WriteLine(column.ColumnName + " = " + row[column]);
        }
        Console.WriteLine("----------------------------------------");
    }
    Console.Read();
}

This will give me details of columns correctly. But when I see BaseTableName for column Id, it is giving contact rather than the alias name c. Is there any way to get the table schema and aliases from a query like the above?

Any help would be great!

Edit

While I could use the execution plan suggested by Rob, I'd appreciate any alternative simple approaches.

Answering questions by tomekszpakowicz

Are you (or your application) source of the query in question? In that case you should know the aliases.

I am not the author of queries. We have a system where users can enter the query. We build columns out of it using the method I explained above. These details will be persisted and another user can use this like adding new criteria etc. So we need to build the SQL dynamically from the information we have. So when a column is aliased and we are not getting alias name, then the where clause constructed will be invalid.

Thanks

Solution

Short answer

This won't work. You cannot, by design, get table aliases from result schema. And you cannot rely on being able to get them from query execution plan.

Long answer

When you get result of a SQL query, the query has already been parsed, validated, optimized, compiled into some internal representation and executed. Aliases are part of query's "source code" and are usually lost somewhere around step 1 and 2.

After query is executed the only things that can be seen as tables are a) real physical tables and b) returned data seen as single anonymous table. Everything between can be transformed or completely optimized out.

If DBMSes were required to retain aliases it would be practically impossible to optimize complex queries.

Possible solutions

I suggest restating a problem:

Are you (or your application) source of the query in question? In that case you should know the aliases.
If you get queries provided by someone else... Well... That depends on why are you adding where causes.
- In the worst case, you'll have to parse queries yourself.
- In the best case, you could give them access to views instead of real tables and put where clauses in the views.

Simple and ugly solution

If I understand your requirements correctly:

User A enters query into your program.
User B can run it (but cannot edit it) and sees returned data. Additionally she can add filters based on returned columns using some kind of widget provided by you.
You don't want to apply filter inside application but instead add them to the query, in order to avoid fetching unnecessary data from database.

In that case:

When A edits query try to run it and gather metadata for returned columns. If ColumnNames are not unique, complain to the author. Store metadata with query.
When B adds filter (based on query metadata), store both columns names and conditions.
At execution:
- Check if filter columns are still valid (A might have changed query). If not remove invalid filters and/or inform B.
- Execute query as something like:
```
 select *
 from ({query entered by A}) x
 where x.Column1 op1 Value1
     and x.Column2 op2 Value2
```

If you want to gracefully handle database schema changes you need to add some additional checks to make sure metadata is consistent with what query really returns.

Security note

Your program is going to pass a query written by user A straight to database. It is crucial that you do it using database connection with permissions which do not exceed A's database permissions. Otherwise you are asking for SQL injection based exploits.

Corollary

If user A doesn't have direct access to the database out of security reasons, you cannot use above solution.

In that case the only way to make it safe is to make sure your application understands 100% of the query which means parsing it in your program and allowing only operations you consider safe.