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, 3rd party tool will be introduced, ApexSQL Search for MySQL and its search capability.

In many cases, during development, there is a need to search through MySQL database 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:

Find all databases in a specific 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>';

Find all tables in a specific MySQL database

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 the 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 all tables in a specific MySQL database which name start with letter 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>')

MySQL information_schema database - find view

Find Indexes

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

SELECT DISTINCT INDEX_NAME
FROM information_schema.STATISTICS
WHERE (TABLE_SCHEMA = ‘‘)

MySQL information_schema database - find indexes

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>');

MySQL information_schema database - find functions

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>';

MySQL information_schema database - find procedures

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>';

MySQL information_schema database - find parameters

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>';

MySQL information_schema database - find triggers

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>';

MySQL information_schema database - find table columns

3rd party tool – ApexSQL Search for MySQL

ApexSQL Search for MySQL is a free MySQL search add-in that can be integrated into Visual Studio and can perform all the above-mentioned search options, just like the MySQL information_schema database with only one difference, there is no need to type any line of code in order to find an object of interest.

ApexSQL Search for MySQL has the Object search feature that can perform the search through MySQL databases based on search criteria set in the Search text box under the Object search panel.

To initiate the Object search feature, in Server Explorer panel, select a database, go to the ApexSQL Search for MySQL main menu and, from the list, choose the Object search command:

Object search command from the ApexSQL Search for MySQL main menu

Another way to open the Object search panel is to select a database in Server Explorer panel, right-click and, from the context menu, choose the Object search command:

MySQL object search command in the Server Explorer context menu

You can use the Ctrl+Shift+Alt+M shortcut or ApexSQL Search for MySQL toolbar with a click on the Object search button to launch the Object search feature:

MySQL object search command in the ApexSQL Search for MySQL toolbar

The Object search panel will appear:

MySQL Object search panel

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

Find MySQL objects based on search criteria

Under the Object search panel, set options to specify where the searched text should look for results, so it can be set to search results in the object name by selecting the Object names checkbox or in the object body by selecting the Object bodies checkbox:

Additional options for search of MySQL objects

The search criteria can be performed over one or more MySQL databases from a particular MySQL instance, by selecting available databases from the Database drop-down box:

Selecting available database from the Database drop-down box

To get search focus on a particular object, type from MySQL database, for example, to search only triggers, in the Object types drop-down box, select the only the object type of interest. In this case, that is the Triggers item, so click the Find button. This will find all triggers for the world and testdb databases:

Find all triggers in the world and testdb databases

To find all parameters for the stored procedures and functions in a MySQL database, in the Object types drop-down box, check the Parameters checkbox and click the Find button:

Find all parameters in the world and testdb databases

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

Besides finding results and displaying those results in the Object search results grid, additionally, the searched objects can be easily located in the Server Explorer panel by using the Navigate to server explorer node command from the right-click context menu:

Navigate to Server Explorer command

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

Save MySQL objects search results

 

February 28, 2020