Get all tables and all columns from odbc database

Question

I want to get all the table names from OdbcConnection, and for all the table names I want to get all the column names.

So, I came across the functionality of OdbcConnection.GetSchema() . I need to get all the table names just using connection.GetSchema("Tables") . But now I want to get column information for these tables. I noticed that connection.GetSchema("Columns") will give me information about the columns, but this only gives it from the random / first (?) "Table" in the data source (using the Windows CSV driver), which doesn’t help much.

The hardest part is working with any (most) ODBC drivers. I will not know which underlying data source will be used.

Any ideas?

Answer 1

Column schema will return all tables

 cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, null, null }); 

Or for one table

 cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, "table1", null }); 

Similarly

 columns = cn.GetSchema("Columns"); 

Returns all columns in all tables.

Additional Information: Schema Limitations

Edit Comments

  string cs = @"Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=z:\docs;"; OdbcConnection cn = new OdbcConnection(cs); cn.Open(); DataTable tables = cn.GetSchema("Tables"); DataTable columns = cn.GetSchema("Columns"); foreach (DataRow row in columns.Rows) { Console.WriteLine(row["COLUMN_NAME"].ToString()); Console.WriteLine(row["TABLE_NAME"].ToString()); } Console.Read();