Query to list fields in an ODBC (Oracle) table?

[CptCrusty1]

I have been tasked with generated a list of all the fields in all of our Oracle tables. We want to build a db to show where all the possible locations are of a specific data. For example the "widgets" field is in the tbl_Product and tbl_Line tables. I want to write a query/code that will provide all the fields of these tables.

Thanks
Crusty.

I live to work and I work to live.

 

[JPMontreal]

I don’t know Oracle but it should be like SQL Server. In every database, there are two tables: one call in SQL sysobjects that list the name and an ID for all objects in the database and an other table call in SQL syscolumns that list all the fields and the ID of the table where you could find that field.
Hope it help.



Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[CptCrusty1]

That could be true, however, I probably don't have access to it as the Oracle gods are VERY stingy. I'll poke around and see what I get.

Thx
Crusty.

I live to work and I work to live.

 

[abaldwin]

Check out the following link

http://www.vttoth.com/oracle.htm

There are steps to list all teh tables and then the describe command will list all the fields.
You will have to do some SQL work to create a table holding the table name, then populate it with a list of the tables you find.

Create a second table with the table name and field name fields.

Loop through the first table and describe it into the second table. Then you will be able to report on the existance of field names and their data type etc.

That is the concept at least that you will need to use. The same concept is used with MS-sQL just from other tables. as mentioned above.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[PHV]

Have a look at the ADOX.Catalog object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886