How to get table name of a column from SqlDataReader

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 ne开发者_如何学编程ed 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);

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

This unanswered question on stackoverflow uses SqlDataReader.GetSchemaTable to get the table name. Their problem is that it returns the actual table name rather than the alias that the table has. Not sure if this works with your sql but figured I'd let you know just in case.

I don't know if this information is available. In particular, not all columns of a result set come from a table. From a relational point of view, tables and resultsets are the same thing.

reader = cmd.ExecuteReader();
reader.GetSchemaTable().Rows[0]["BaseTableName"];

In general, this is not possible. Consider the following query:

SELECT col1 FROM table1
UNION ALL
SELECT col1 FROM table2

Clearly col1 comes from more than one table.

you can solve it like the following :

DataTable schemaTable = sqlReader.GetSchemaTable();

foreach (DataRow row in schemaTable.Rows)
{
    foreach (DataColumn column in schemaTable.Columns)
    {
        MessageBox.Show (string.Format("{0} = {1}", column.ColumnName, row[column]));
    }
}

SqlCeConnection conn = new SqlCeConnection("Data Source = Database1.sdf");
SqlCeCommand query = conn.CreateCommand();
query.CommandText = "myTableName";
query.CommandType = CommandType.TableDirect;
conn.Open();
SqlCeDataReader myreader = query.ExecuteReader(CommandBehavior.KeyInfo);
DataTable myDataTable= myreader.GetSchemaTable();
//thats the code you asked. in the loop
for (int i = 0; i < myDataTable.Rows.Count; i++)
{
    listView1.Columns.Add(myDataTable.Rows[i][0].ToString());
}

How to get database name, table name & column name. Also possible to get Schema name as well. Tested with MS SQL 2016

CommandBehavior.KeyInfo must be indicated

        SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.KeyInfo);

        DataTable dataTable = sqlDataReader.GetSchemaTable();

        for (int i = 0; i < dataTable.Rows.Count - 1; i++)
        {
            string ii =
                dataTable.Rows[i]["BaseCatalogName"].ToString() + "\\" +
                dataTable.Rows[i]["BaseTableName"].ToString() + "\\" + 
                dataTable.Rows[i]["ColumnName"].ToString();
        }