Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Finding tablenames for ODBC-datasource

Posted on 2002-07-11
10
Medium Priority
?
364 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 1500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Loops Section Overview
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

885 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