How to get table name of a column from SqlDataReader

Question

I have an SQL query I get from a configuration file, this query usually contains 3-6 joins.

I need to find at run time, based on the result set represented by SqlDataReader, to find the name of the table for each column.

Here are some thing that don't work:

• SqlDataReader.GetName returns the column name but not the table name.
• SqlDataReader.GetSchemaTable returns a data table with column information - but all the table names are null.
• Querying information_schema doesn't help because I need data on the results of the current query (and the column names are not unique - there are columns with the same name in different tables).

I'm using .net 3.5SP1/ C#/ SQL Server 2008 in a console application.

EDIT: I know this is not possible for all cases since a "column" can be combined from multiple tables, a function or even a constant expression - I'm looking for something that works in the simple case.

EDIT 2: Found out why it didn't work - You can use SqlDataReader.GetSchemaTable to get table information but you have to set CommandBehavior to KeyInfo, you do that in the ExecuteReader call:

reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

Answer 1

You can use SqlDataReader.GetSchemaTable to get table information but you have to set CommandBehavior to KeyInfo, you do that in the ExecuteReader call:

reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);