Use MySQL information_schema to perform object search

In this article, it will be shown how to find objects using the MySQL information_schema database and retrieve information related to that specific object. In the second part of the article, 3^rd party tool will be introduced, ApexSQL Database Power Tools for VS Code and its search capability.

In many cases during development, there is a need to search through MySQL databases to retrieve database information for MySQL objects such as tables, table columns, views, users, indexes, functions, procedures, etc.

Object search using the MySQL information_schema database

Find all MySQL databases

To list all available databases on MySQL instance, execute the following code:

SELECT SCHEMA_NAME
FROM information_schema.SCHEMATA;

This code will show all databases for a MySQL instance:

The same result can be achieved by using the code below:

SHOW DATABASES;

Find tables using MySQL information_schema

To list all tables from the MySQL database, execute the code below:

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<database_name>';

The same result can be got by using the code below:

USE world;
SHOW TABLES;

To perform object search in MySQL database for specific tables, for example, search for all tables which name starts with letter C, execute the following MySQL code:

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<database_name>' AND TABLE_NAME LIKE 'C%';

Find view using MySQL information_schema

To perform a search for all views under the MySQL instance, use the code below:

SELECT TABLE_NAME
FROM information_schema.VIEWS

But, to narrow down the search scope to just one database, add one extra line of code:

SELECT TABLE_NAME
FROM information_schema.VIEWS
WHERE (TABLE_SCHEMA = '<database_name>')

Find Indexes

Below is the query which will retrieve all MySQL indexes from the database. Primary key indexes will have the same name unless given an explicit name when created:

SELECT DISTINCT INDEX_NAME
FROM information_schema.STATISTICS
WHERE (TABLE_SCHEMA = '<database_name>')

Find Functions

To retrieve all functions under a MySQL database, execute the code below:

SELECT ROUTINE_NAME
FROM information_schema.ROUTINES
WHERE (ROUTINE_TYPE = 'FUNCTION') AND (ROUTINE_SCHEMA = '<database_name>');

Alternatively, the following code can be used:

SHOW FUNCTION STATUS WHERE DB = 'world';

Find Procedures

The code for finding all procedures under a MySQL database is similar to the code for finding all functions, just in the Where clause, under the ROUTINE_TYPE, set the PROCEDURE value:

SELECT ROUTINE_NAME
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = '<database_name>';

Alternatively, the following code can be used:

SHOW PROCEDURE STATUS WHERE DB = 'world';

Find Parameters

Under the PARAMETERS table of the MySQL information_schema database, information related to stored procedures and function parameters can be found. The PARAMETERS table does not include built-in SQL functions or user-defined functions (UDFs):

SELECT SPECIFIC_NAME ,ROUTINE_TYPE ,PARAMETER_NAME
FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = '<database_name>';

Find Triggers

The code below finds all triggers for a database specified in the Where clause:

SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = '<database_name>';

Another way to view all triggers is to use this code:

SHOW TRIGGERS;

Find table Columns

To find columns in a table, the COLUMNS table from the MySQL information_schema database can be used.

SELECT COLUMN_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '<database_name>' AND TABLE_NAME = '<table_name>';

3^rd party tool – ApexSQL Database Power Tools for VS Code

ApexSQL Database Power Tools for VS Code extension integrated into Visual Studio Code and can perform all above-mentioned search options, just like the MySQL information_schema database with only one difference, you do not need to type any line of code in order to find an object of interest.

ApexSQL Database Power Tools for VS Code has the Object search feature that can perform the search through MySQL or MariaDB databases based on search criteria set in the Search phrase box under the Object search pane.

To initiate the Object search feature, in the ApexSQL server explorer pane, select a database, right-click, and, from the context menu, choose the Object search command:

The Object search pane will appear:

In the Search phrase box, enter the searched string and click the Find button:

On the Object search pane, options to specify where the searched text should look for results can be found, so it can be set to search results in the object name by selecting the Object names check box or in the object body by selecting the Object bodies check box:

To get search focus on a particular object, type from MySQL database, for example, to search only triggers, in the Search for object types section, select the only the object type of interest. In our case, that is the Triggers item, so click the Find button. This will find all triggers for the sakila:

To find all parameters for the stored procedures and functions in a MySQL database, in the Search for object types section, check the Parameters check box and click the Find button:

As can be seen, the results are the same as we used for the PARAMETERS table from the MySQL information_schema database.

Except by finding results and displaying those results in the Object search results grid, additionally, the searched objects can be easily located in the ApexSQL server explorer pane by click on the value in the Object search results grid:

Results from the Object search results grid can be copied or saved in one of the available formats like Excel, JSON, HTML, CSV: