Author: Prometheus Group
CATEGORY: Tutorials
July 15, 2022

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

Author: Prometheus Group
CATEGORY: Tutorials
September 5, 2019

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

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.

IBM Maximo Find Columns Search Results
IBM Maximo CMMS Results

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!

Subscribe so you never miss a new post

Similar Posts

Headquarters - Raleigh, NC
4601 Six Forks Rd Ste 220
Raleigh, NC 27609
Phone: +1 (888) 317-2397
San Antonio, TX
18880 Stone Oak Pkwy
San Antonio, TX 78258
Phone: (210) 201-8189
Mundelein, IL
405 Washington Blvd Ste 203
Mundelein, IL 60060
Phone: (847) 388-3600
Gateshead, UK
Baltic Place, S Shore Rd. Gateshead
NE8 3AE, United Kingdom
Phone: +44 191 201 7777
Leeds, UK
1 York Pl, Leeds
LS1 2DR, United Kingdom
Phone: +44 113 827 0080
Melbourne, AU
16 Kirk St, Moe VIC
3825, Australia
Phone: +61 3 5132 2600
Brisbane, AU
8A/36 Agnes St, Fortitude Valley
QLD 4006, Australia
Phone: +61 7 3188 5264
Newstead, AU
Level 8, Skyring Terrace
QLD 4006, Australia
Phone: +61 7 3188 5264
Hamilton, CA
50 Dundas St E, Dundas, ON
L9H 7K6, Canada
Phone: +1 905-544-4144
Dordrecht, NL
Burgemeester de Raadtsingel 67, 3311 JG
Dordrecht, Netherlands
Phone: +31 78 614 6288
Kerkrade, NL
Eurode-Park 1, Kerkrade, Limburg 6461
KB, Netherlands
Phone: +31 78 614 6288
São Paulo, BZ
R. Olimpíadas, 205, 4. Andar
Vila Olimpia, São Paulo, SP, CEP 04551-000
Phone: +55 11 3728-9372
Pretoria, SA
1 Pegasus Building 210 Amarand Ave
Waterkloof Glen, Pretoria, 0181
South Africa
Phone: +27 12 003 2916
Bangalore, IN (North)
110 JB Building 4th Cross Rd
5th Block Koramangala
Bengaluru, Karnataka, 560095
Phone: +91 9886300245
Bangalore, IN (South)
No. 92 ZED Square Jyothi Nivas College Rd
5th Block Koramangala
Bengaluru, Karnataka, 560095
Phone: +91 9986150900