Find Columns in All Tables With This Oracle Script for IBM Maximo

There’s one thing virtually every maintenance Planner or Scheduler can agree on, whether you work in Utilities, Facilities Management, or Oil and Gas: you deal with tons of data. Personally, I’ve never seen a Planner or Scheduler whose dataset has, say, two work orders.

I mentioned in my previous article that I’ve seen implementations with over 1,700 tables, some of which had 800 columns, when using multiple Maximo industry add-ons.

So, if you’re trying to search through all those tables to find one particular column – you better hope you don’t have to do it manually. Because that would be a real time-waster.

I was looking for something that would be the equivalent of a search in Windows. So, a couple weeks back, I published an article detailing how to find columns in all tables using a SQL script for IBM Maximo and other databases.

Later in the week, my inbox started filling up with emails like “This isn’t working for me!” and it makes sense, because the script was written for MSSQL. So, let’s talk about a similar script for Oracle.

And just like the script for SQL, it has to return results in a matter of milliseconds – so that you have more time to get back to value-added activities.
‍

Use This Oracle Script To Search For Columns in IBM Maximo or Any Oracle Database

So, without further ado, here’s the script:

select utc.column_name as colname, uo.object_name as table name

from user_objects uo

join user_tab_columns utc on uo.object_name = utc.table_name

where uo.object_type = 'TABLE' and utc.column_name like upper('FIELD_NAME');

All you have to do is replace FIELD_NAME with the name of the column you want to find. (Hint: you can use a part of the name if you use “wildcards” to wrap the value.)

Here are two examples showing just how fast this search can be. In the first example, it returns seven rows in 0.047 seconds. In the second, it returns 42 rows in the same amount of time.

Note: These screenshots were previously taken using Solufy's AWKIRE solution (now Prometheus Routine Maintenance), but they should be instructive for any CMMS.

So, there it is. A handy way to search through your tables quickly. Now, I’m sure there are other ways to achieve the same thing; this is just the script that I’ve had success using in IBM Maximo and other Oracle databases.

Interested in an enterprise asset management solution that integrates with IBM Maximo and Oracle? Request a demo of the Prometheus platform!