Dashboards - Business Intelligence

How to find a column in database

Jan 17, 2007 ERP, ERP Tables

Once, at a client site I was asked to do a drill through report from Purchasing to Fixed Assets. I don’t remember the exact details but it had something to do with Purchase orders. I knew little bit about Oracle Purchasing but had not worked enough on Fixed Assets. So I thought maybe I should start reading the FA user guides and the FA technical reference manuals. Most of my knowledge in ERP is gained through reading the User guides and TRMs but having to go through them requires significant patience.

First I had to find out which table stores the relevant PO details and then figure out how the information can be linked back to Purchasing. The client kept asking me on how to get started and I asked for some time to research on the drill link. I started reading the manuals but eventually gave up as I couldn’t locate it(not that it did not have the information).

Suddenly browsing through the table structure in the database, I realized that maybe I can run a query against the Database meta data and find out if there is a PO related item in a FA related table.

So I ran the below query

“select * from dba_tab_cols all_tab_cols where table_name like ‘FA%’ and column_name like ‘PO%’ ”

Bingo! This gave me a list of all the possible Fixed Assets table which contained the Purchasing columns in it. This became the starting point for my research and eventually traced back to the root information, built the queries and finally informed the client.

So what is dba_tab_cols? This is a database meta table maintained by Oracle database itself. It stores every column name for each table name in the database. You can query your Oracle database right now to see how it looks.

Before leaving the site, I showed the trick to the client on how this was accomplished and told him that this way you can find information yourself rather than calling anybody to help. He was really happy to have found this new “Magic wand”

Since then this trick has become my consulting arsenal. You shoulld too make it a habit of using it to stay sharp and ahead of others.

I developed similar queries for Oracle Flexfields and Discoverer which I will post soon.