Listing all Tables in a Database

[Gimpster]

I have a list box and I want to be able to automatically list all available tables in a certain database. I must use some form of code that allows for adding and deleting tables, and then updates the list accordingly. Please give some code, if you can. Thanks.

------------------
Ryan
corneslen@hotmail.com
ICQ# 47799046

[JHausmann]

What database are you planning in using?

[Gimpster]

I'm going to be using Sybase SQL Anywhere ver. 11 or 12. I believe the command structure is similar to Microsoft SQL, if that helps. Even if you just know how to do it for Microsoft SQL, I'll take that and try to make it work for Sybase. Thanks.

------------------
Ryan
corneslen@hotmail.com
ICQ# 47799046

[JHausmann]

For SQL Server the following will list all tables in the database:

"select name from sysobjects where type='U'"

SQL to delete a table

"drop tablename"


Adding a table is a bit more complex.

[Gimpster]

That looks like it just updates a list of tables that you already have displayed in a list box. But how do you create the list to begin with?

------------------
Ryan
corneslen@hotmail.com
ICQ# 47799046

[JHausmann]

Actually it's SQL to do 2 things, get all table entries and delete (drop) a table.

If you're not using a bound control, set up a routine that populates your listbox by stepping thru the get query (after using the clear method on the listbox). If you delete (or add) a table, just run that routine any time you do.

[Clunietp]

I did this using ado and the jet provider, it should work with SQL also

Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database.mdb"
cn.Open

Set rs = cn.OpenSchema(adSchemaTables)

Do Until rs.EOF = True
MsgBox rs!TABLE_NAME
rs.MoveNext
Loop

Check out the ADO OpenSchema method, lots of good db info can be had

Tom

[Gimpster]

It looks like VB is having a problem with the Provider. Can you please explain what this is? Mayber I need to change it to work with Sybase SQL Server. Thanks

------------------
Ryan
corneslen@hotmail.com
ICQ# 47799046

[JHausmann]

It tells ADO what drivers to use to make the connection. You will have to change it to use your Sybase ADO drivers (you have them, right?). If you have VB6, you should be able to get the correct value (for Sybase) by using the dataview feature to create a new data environment connection (I cannot tell you what the Provider should be because I don't have Sybase DB's).

[Gimpster]

Ok, thanks

------------------
Ryan
corneslen@hotmail.com
ICQ# 47799046