Finding tablenames for ODBC-datasource

Don't know if these will work for you since I think they are tied to SQL Server but these are a few that I use:

List of Tables:
select * from sysobjects where type = 'U'

Stuff I want to know about the fields in a table:
select sysobjects.name as 'Table', syscolumns.name as 'Field', syscolumns.isnullable, systypes.name as 'Data Type', syscolumns.length from sysobjects left join syscolumns on sysobjects.id = syscolumns.id join systypes on syscolumns.xtype = systypes.xtype where sysobjects.name = 'MyTable' order by syscolumns.colid

Is this really a .NET question?

MS Query or any other ODBC consumer uses ODBC API calls to get the info you want, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcodbc_function_summary.asp and look at the SQLTables and SQLColumns functions.  

Unless you can make ODBC API calls direct to your DSN that's not much use.  The actual SQL, or other code that is sent to the db depends on the ODBC driver you are using.

I suspect you question is more like, how do I get a list of tables/columns from a .NET app, or a VB app  - or whatever.  In which case it really depends on you DMBS, SQL Server has procedures like sp_catalogs_rowset and the sysobjects or INFORMATION_SCHEMA tables to provide this kind of information.

Use the DSN to connect to the database.  Once you have a dataset object, you can capture its Tables property to get a DataTableCollection, iterate through those DataTable objects to get their Columns properties, then grab information on those DataColumns.

AzraSound - how do you create a dataset that contains everything in your database?

Never mind...doesnt do you any good unless you would already know the table names.

Well then I am not aware of any new ways utilizing only .NET.  In prior versions, you could use ADOX to grab this info, sample here:

http://www.vbdiamond.com/Sources/ViewSource.asp?ID=14598


The code only allows for opening an Access97 or Access2000 DB, but if you look at the code where it sets the connection strings, you can just alter it to point to any DSN and it should work fine.

The question is actually how do I make an ODBC-native call using .Net.

AzraSound: ADO is Ok but I dont know how to find the provider from the ODBC source.

Of course.
The solution became:

Step 1: Add the ADO .NET component as reference

               conn.Open("DSN=" & DSN)

               Dim recSet As ADODB.Recordset

               recSet = conn.OpenSchema(SchemaEnum.adSchemaTables)

               Do While Not recSet.EOF
                    result.Add(recSet.Fields(2).Value, recSet.Fields(2).Value)
                    recSet.MoveNext()
               Loop

               conn.Close()