get database information

[thebloke]

Hi guys

A long shot, maybe, but is there any way that I can get details of all of the tables in a database by running a query?

The problem is this: I use an informix database hosted by a third party. They're happy for me to access however they're not very forthcoming with any sort of database design information so I don't know which columns are in which tables.

I know that if it were a sqlserver database I could run sp_help to get this sort of information however is there any query I can run in asp or vb that will give me this information?

ps, I've connected using ado.

Tar!

[Ecniv]

You could loop through all the tables using Adox (if they've installed it) and output to the screen. Then loop through the columns on each table (listing under the table name?).

[thebloke]

Er, ok.....how do I go about this?!

[PilgrimPete]

This article might help.

As far as ADOX goes, you'll need a reference to msadox.dll (Microsoft ADO Ext. 2.8 for DDL and Security) and you can get all sorts of schema info.
However, ADOX was designed and built for Access, so other DBMS are patchily supported. There are loads of posts in this forum about how best to use it.

[randem]

thebloke,

Open the database using ADO and try this:

Dim dbSchema as ADODB.Recordset
          Dim i as Integer
 
          Set dbSchema = New ADODB.Recordset
          dbSchema = db.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
 
           For i = 0 to dbSchema.Fields.Count - 1
                Debug.Print dbSchema.Fields(i)
           Next i

Enumerate through the fields property for the names of the columns.

[thebloke]

I'll give it a go. Tar.