asked on

Database - Retrieve List of Tables

Hello Experts,

I'm writing a C DLL to interface with my SQL Sever 2000 via the ODBC API. I successfully connected to the Server and am able to display a list of available Databases to choose from. Now, I need to display a list of tables for the database that I choose. To get the list of Databases, I used SQLBrowseConnect(), but I know I've gone as far as I can with this function and will need another function (or method) to retrieve the tables.

Any ideas?

Thank you for considering this question.

Kind Regards,
favic

JohnChapin

Favoc,
once you connect to the database, you can use this SQL to find all the user defined tables:

select * from sysobjects where xtype = 'U'

You will have to connect as a user SA or DBO to get past security, I believe.

Need More? Let me know.
HTH John

ASKER CERTIFIED SOLUTION

Brimba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

use the information_schema views rather than direct access on Sysobjects

you don;t have to be SA or DBO, you just need your id to be allowed read access to the Information_Schema views...

favic

ASKER

Hello All,

Thanks for the incredibly quick reply.
What I'm actually looking for is a function call, not an SQL Statement. The reason being, I can extract the information from a string returned from the call much like the way it's done using SQLBrowseConnect(). As for the suggestions you guys gave, I must retrieve the results through a query. I'm not quite sure yet how to receive the results from a query that has an unknown number of rowsets and from unknown column types.

I need to display the entire result in a textbox, separated by commas as part of my GUI.

Thanks.
favic

JohnChapin

Favic
I expect that you will be displaying the tables only for the selected database in a 'previous' control.
This means that the change in selecion of the database will re-execute the query on the new database.
and the new data will show in the data display.

We really need to know what language and DB type you are using, since SQLserver doesn;t use schema, just oracle.
I need more! Let me know ;-)
John

favic

ASKER

JohnChapin

Yes, you are right. I want to be able ( in my GUI ) to select a database from a ListBox...this had already been implemented. Now, when the database is chosen, I want to be able to either diplay the list of tables in a Textbox (separated by commas) or in another ListBox.

The language I'm using to code my DLL is in C. I'm accessing the ODBC API calls to allow me to communicate with a remote SQL Server 2000.

Let me know if you need more info.

Thanks
favic