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.

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:

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!