Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Finding tablenames for ODBC-datasource

Posted on 2002-07-11
10
Medium Priority
?
366 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

577 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