Get all tables and all columns from a odbc database

Question

I want to get all "table" names from a OdbcConnection, and for all "table" names I want to recieve all column names.

So I came across the OdbcConnection.GetSchema() functionallity. I manges to get all the table names by simply using connection.GetSchema("Tables"). But now I want to get the column information for those tables. I noticed connection.GetSchema("Columns") will give me columns information, but this only gives it from a random/first (?) "table" in the datasource (using Windows CSV driver), which doesn't help very mutch.

The most challenging part is, that would have to work with any (most) ODBC drivers. I won't know which underlying datasource will be used.

Any ideas?

Stannite

Answer 1

The column schema will return all tables

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

Or for a single table

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

Similarly,

columns = cn.GetSchema("Columns");

Returns all columns in all tables.

More info: Schema Restrictions

Edit re 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();

Greenling

I get the columns from my two test "tables", that is, text files. See additional example. – Greenling

@Fionualla: Is it possible to return the tables that their name contain a specific word? – Casilde

Answer 2

I know it's too late to help the original poster but if it helps anyone else I made a mini project that goes through all the ODBC Tables and creates a class for each one with the column names as string fields. You can use these classes to compose string queries.

Link to project

Jiggle