How to Find Columns in All Tables – Fast! – With This Oracle Script for IBM Maximo and More

Author: Elliot Bonilla
Category: Tutorials

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 tablename

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.

IBM-Maximo-Find-Columns-Search-Results-1.png
IBM-Maximo-CMMS-Results-2.png

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.

Author: Elliot Bonilla

Elliot Bonilla's expertise extends to over 12 years in system analysis, product development, and engineering roles. Elliot draws from his considerable experience in various sectors like Pharma, DOD, DOE, NASA, and Enterprise projects to ensure customer success.

His extensive knowledge of IBM Maximo CMMS allows our customers to leverage the vast features of Prometheus Routine Maintenance. Working alongside the Sales, Client Services, and Development teams, Elliot ensures that every implementation, upgrade, and support call is handled with care and due diligence.

Originally from Puerto Rico, Elliot now lives in Florida. In his spare time, he enjoys working on Maker/DIY projects and spending time with his family.

Similar Posts