AKWIRE for Maximo User Group

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

by Elliot Bonilla on January 18, 2018

 

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.

via GIPHY

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

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! 

Do you have any nifty tricks for IBM Maximo that you’d like to share? Let us know your tips in the comments, and if you’d like to get more advice on how to make Maximo work for you, see my other blog articles here.  

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