Solved

Finding tablenames for ODBC-datasource

Posted on 2002-07-11
10
359 Views
Last Modified: 2008-02-26
How do I find the table names and field names for an ODBC DSN? (It must be doavle, Microsoft Query does it).
0
Comment
Question by:Llandr
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 2

Expert Comment

by:Check
ID: 7146187
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

0
 
LVL 4

Expert Comment

by:kpkp
ID: 7146238
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.

0
 
LVL 28

Expert Comment

by:AzraSound
ID: 7146249
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.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 4

Expert Comment

by:kpkp
ID: 7146313
AzraSound - how do you create a dataset that contains everything in your database?
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 7146318
Never mind...doesnt do you any good unless you would already know the table names.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 7146439
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.
0
 
LVL 2

Author Comment

by:Llandr
ID: 7171747
The question is actually how do I make an ODBC-native call using .Net.
0
 
LVL 2

Author Comment

by:Llandr
ID: 7171906
AzraSound: ADO is Ok but I dont know how to find the provider from the ODBC source.
0
 
LVL 28

Accepted Solution

by:
AzraSound earned 500 total points
ID: 7172081
Do you need to know the provider?  I thought the original question was in regards to finding out tables and fields given the DSN.  Once you have the DSN, you should be able to connect to any data source as long as the necessary ODBC driver is installed.
0
 
LVL 2

Author Comment

by:Llandr
ID: 7173527
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()
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question