MySQL Utilities

5.16 mysqlmetagrep — Search Database Object Definitions

This utility searches for objects matching a given pattern on all the servers specified using instances of the --server option. It produces output that displays the matching objects. By default, the first non-option argument is taken to be the pattern unless the --pattern option is given. If the --pattern option is given, then all non-option arguments are treated as connection specifications.

Internally, the utility generates an SQL statement for searching the necessary tables in the INFORMATION_SCHEMA database on the designated servers, and then executes it before collecting the result and printing it as a table. Use the --sql option to have mysqlmetagrep display the statement, rather than execute it. This can be useful if you want to feed the output of the statement to another application, such as the mysql client command-line tool.

The MySQL server supports two forms of patterns when matching strings: SQL Simple Patterns (used with the LIKE operator) and POSIX Regular Expressions (used with the REGEXP operator).

By default, the utility uses the LIKE operator to match the name (and optionally, the body) of objects. To use the REGEXP operator instead, use the --regexp option.


Because the REGEXP operator does substring searching, it is necessary to anchor the expression to the beginning of the string if you want to match the beginning of the string.

To specify how to display output, use one of the following values with the --format option:

SQL Simple Patterns

The simple patterns defined by the SQL standard consist of a string of characters with two characters that have special meaning: % (percent) matches zero or more characters, and _ (underscore) matches exactly one character.

For example:

POSIX Regular Expressions

POSIX regular expressions are more powerful than the simple patterns defined in the SQL standard. A regular expression is a string of characters, optionally containing characters with special meaning.

Documenting these regular expressions goes beyond the scope of this manual, but the full syntax is described in the MySQL manual and other locations, such as executing 'man regex' in your terminal.


mysqlmetagrep accepts the following command-line options:


For the --format option, the permitted values are not case-sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).


Find all objects with a name that matches the pattern 't_' (the letter t followed by any single character):

shell> mysqlmetagrep --pattern="t_" --server=john@localhost
| Connection             | Object Type  | Object Name  | Database  |
| john:*@localhost:3306  | TABLE        | t1           | test      |
| john:*@localhost:3306  | TABLE        | t2           | test      |
| john:*@localhost:3306  | TABLE        | tm           | test      |

To find all object that contain 't2' in the name or the body (for routines, triggers, and events):

shell> mysqlmetagrep -b --pattern="%t2%" --server=john@localhost:3306
| Connection             | Object Type  | Object Name  | Database  |
| john:*@localhost:3306  | TRIGGER      | tr_foo       | test      |
| john:*@localhost:3306  | TABLE        | t2           | test      |

In the preceding output, the trigger name does not match the pattern, but is displayed because its body does.

This is the same as the previous example, but using the REGEXP operator. Note that in the pattern it is not necessary to add wildcards before or after t2:

shell> mysqlmetagrep -Gb --pattern="t2" --server=john@localhost

| Connection             | Object Type  | Object Name  | Database  |
| root:*@localhost:3306  | TRIGGER      | tr_foo       | test      |
| root:*@localhost:3306  | TABLE        | t2           | test      |


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