Solved

Finding tablenames for ODBC-datasource

Posted on 2002-07-11
10
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

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!

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

615 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