[RESOLVED] [2005] Database tables where I don't know table names

[penguin5000]

Hi,
I am trying to figure out how, when I've connected to a database, I can collect all of the tables from that database, and put them into a TreeView, or ListView, or ListBox, or whatever I choose.
Ideally, I'd like to be able to list the table, and then list all fields within it, and then the field properties (i.e. string, numeric, length, etc. etc. etc.)
Can anybody point me in the right direction?
Thanks.

[bmahler]

what type of database are you using? I know in SQL Server you can get the Table names by running this SQL

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

As for access or other db's I am not exactly sure.

EDIT: This SQL will return you all the table, column name and datatype for all tables in the database but once again, this is for SQL server

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS

OR for information of a specific table

SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YOUR_TABLE_NAME'

[bmahler]

Well, also for access you can get the table list like so

SELECT  Name FROM MSysObjects WHERE (Left([Name],1)<>"~") AND (Left([Name],4) <> "MSys") AND ([Type] In (1, 4, 6)) ORDER BY Name

[penguin5000]

Thanks for your reply.
I'm using an Informix database, and I'm presuming that tables aren't identified as being TABLE_NAME - unless I'm doing something wrong - which is possible, I guess

[GaryMazzone]

Information_Schema is unique to SQL Server only. The metadata for Informix is stored differently.

[penguin5000]

OK - thanks. I'll have to post something on an Informix forum - they must have come across this problem, surely?

[GaryMazzone]

There is a method. I think you look in the catalog somewhere I just don't use Informix so I can't say where you would look.

[nbrege]

Try "select * from systables". That works on our Informix database.

[penguin5000]

Ahh - thanks guys.