Author: Elliot Bonilla
CATEGORY: Tutorials
September 5, 2019

Find Columns in All Tables With This Handy SQL Script for IBM Maximo, and Other Database

Author: Elliot Bonilla
CATEGORY: Tutorials
September 5, 2019

Find Columns in All Tables With This Handy SQL Script for IBM Maximo, and Other Database

IBM Maximo stores massive amounts of data. Massive. In a typical out of the box set-up, you’re looking at 500+ tables, each of which could have hundreds of columns. Implementations in Utilities, Facilities Management, or Oil and Gas settings can have even more tables depending on how many add-ons you have. I’ve seen implementations with over 1,700 tables, each with up to 800 columns. That’s a practically inconceivable amount of information.

Which means if you want to search through all that information for one particular field, it’s like looking for a needle in a Maximo haystack.

How do you find that needle? Use this.

This is a useful SQL script that will search through all the tables in a database for a specific field name and return the tables where that field is found. Think of it as the Maximo equivalent to a search in Windows.

In Windows, you can search for files by name, by keywords, by folders with words in it, etc. With this script, you can do something similar. It will find columns with your search term in all of your tables within that application.

Keep in mind, there may be other ways to do this. But this is our neat trick. Plus, it doesn’t just work for IBM Maximo. You can also use this same script in other CMMS systems. But for our intents and purposes, we’re focusing on Maximo.

Searching all columns in IBM Maximo: the secret SQL script

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

select c.name as colname, t.name as tablename

From sys.columns c

join sys.tables t on c.object_id = t.object_id

Where c.name like 'FIELD_NAME';

Replace FIELD_NAME with the name of the column you are looking for. Or you can use a part of the name if you use “wildcards” to wrap the value.

Here’s an example where a few results are returned. You can see the hit count on the lower right, as well as the time that it ran.

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

script-IBM-Maximo-SQL-search.jpg

Of course, if you can find what you’re looking for in less time browsing manually, then you don’t need this script for IBM Maximo. But I’m guessing you can’t, since this search ran and delivered its results in milliseconds – literally.

Here’s an example with a lot of hits:

Sql-Script-IBM-Maximo-CMMS-results.jpg

If you’ve worked in Maximo CMMS, then you know just how much data it stores. We’ve always got efficiency on the brain. We’re constantly thinking about how to increase IBM Maximo efficiency for Maintenance Planners and Schedulers, so that in turn, they can optimize wrench time for technicians. With that in mind, scrolling through tons of IBM Maximo tables is not the way to go.

So, enjoy this script!

Latest Resources

Similar Posts

Offices
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
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
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