AKWIRE for Maximo User Group

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

by Elliot Bonilla on February 22, 2018

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, AKWIRE, 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, AKWIRE 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.

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, AKWIRE, and other Oracle databases.

By the way, we’re always interested in hearing from our readers. If you want to share your tricks and tips for IBM Maximo and AKWIRE, let us know in the comments.

Subscribe to the Solufy blog

Elliot Bonilla

Director of Technical Support, Solufy.

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

Read More..