MySQL Utilities

3.3.6 How do you find all objects that start with a given name prefix?

One of the challenges for database administrators who manage servers with thousands of objects is the task of finding an object by name. Sometimes all you have to go on is the name of a table or perhaps an obscure reference to a partial name. This can come about through a diagnosis of a problem connection, application, or via an incomplete description from a defect report.

It is also possible you need to simply check to see if certain things exist. For example, suppose among your databases are parts or inventory data and you want to check to see if there are any functions or procedures that operate on a column named 'cost'. Moreover, you want to see anything related that has 'cost' as part of its name.

Whatever the case, it would be a big time saver if you could search through all of the database objects and see a list of the objects whose name matches a prefix (or pattern). Fortunately, the mysqlmetagrep utility can get this done.


Find all objects whose name begins with a known prefix. More specifically, find any mention of the word 'cost'.

Example Execution

shell> mysqlmetagrep --server=root:root@localhost --body --pattern='%cost%'
| Connection             | Object Type  | Object Name  | Database | Field Type  | Matches          |
| root:*@localhost:3306  | FUNCTION     | adjust_cost  | griots   | ROUTINE     | adjust_cost      |
| root:*@localhost:3306  | TABLE        | supplies     | griots   | COLUMN      | cost             |
| root:*@localhost:3306  | TABLE        | film         | sakila   | COLUMN      | replacement_cost |

shell> mysql -uroot -proot -e "SHOW CREATE FUNCTION griots.adjust_cost \G"
*************************** 1. row ***************************
            Function: adjust_cost
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `adjust_cost`(cost double)
return cost * 1.10
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci      


In this example, we see the use of the database pattern '%cost%' to find objects that have 'cost' anywhere in their name. We also see the use of the --body option to instruct the utility to look inside procedures and functions. This can be very handy to locate routines that manipulate data as you can see.

Notice once we found a routine that had 'cost' mentioned, we can examine its body via the SHOW CREATE FUNCTION command to see just how it is using the column 'cost'. In this case, we see someone has written a function to adjust the cost by 10%'.

Therefore, not only can you find objects that have anything named 'cost', you can also discover any hidden logic that may operate on something named 'cost'.

Permissions Required

The user must have the SELECT privilege on the mysql database.

Tips and Tricks

If you are familiar with using regular expressions, you can use the --regexp option to use regular expressions instead of database patterns. For example, the regular expression for the search above would be --pattern='^.*cost.*' --basic-regex.