Finding tables, views, functions, procedures and triggers in MySQL Database

This article is about identifying database objects using a stored procedure.

A database consists of database objects, let's take them by definition:

Table: In relational databases and flat file databases, a table is a set of data elements (values) that is organized using a model of vertical columns.

View: A view is a named, derived table whose definition is a persistent part of the database.

Trigger: A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.

Stored Procedure / Function: Collectively known as Stored Routine, is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored routine instead.

To know what objects a particular database consists of, we can inquire the schema with available syntaxes to access the metadata.

A Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges.

In mysql we have the information_schema database, storing information about all other databases handled by the server.

So, when we want to know details about databases on the mysql server, we may surely inquire the information_schema. To make this "inquiry" task simple I've written one more database object, a stored procedure, which will inquire information_schema and provide us with summery of database objects.

Following is a simple stored procedure that summaries the objects of a database with a single function.
The procedure gives a quick idea of available Tables, Views, Triggers, Stored Procedures and Functions from a database.

Steps:
1. Create procedure from attached code.

DELIMITER $$
                      
                      DROP PROCEDURE IF EXISTS `xplore` $$
                      CREATE DEFINER=`root`@`localhost` PROCEDURE `xplore`(IN_DB varchar(100))
                      BEGIN 
                      DECLARE DB VARCHAR(100);
                      DECLARE NO_TABLES INT(10);
                      DECLARE NO_VIEWS INT(10);
                      DECLARE NO_FUNCTIONS INT(10);
                      DECLARE NO_PROCEDURES INT(10);
                      DECLARE NO_TRIGGERS INT(10);
                      DECLARE SUMMARY VARCHAR(200); 
                      SET DB=IN_DB;
                      
                      drop temporary table if exists objects;
                      create temporary table objects
                      (
                      object_type varchar(100),
                      object_name varchar(100),
                      object_schema varchar(100)
                      )
                      engine=myisam; 
                      
                      INSERT INTO objects
                      /* query for triggers */
                      (SELECT 'TRIGGER',TRIGGER_NAME ,TRIGGER_SCHEMA
                      FROM information_schema.triggers
                      WHERE TRIGGER_SCHEMA like DB)
                      
                      UNION
                      
                      /* query for views*/
                      (SELECT 'VIEW', TABLE_NAME,TABLE_SCHEMA
                      FROM information_schema.tables
                      WHERE table_type='VIEW' and TABLE_SCHEMA like DB)
                      
                      UNION
                      
                      /* query for procedure*/
                      (SELECT 'PROCEDURE', SPECIFIC_NAME, ROUTINE_SCHEMA
                      FROM information_schema.routines
                      WHERE routine_type='PROCEDURE' and ROUTINE_SCHEMA like DB)
                      
                      UNION
                      
                      /* query for function*/
                      (SELECT 'FUNCTION', SPECIFIC_NAME, ROUTINE_SCHEMA
                      FROM information_schema.routines
                      WHERE routine_type='FUNCTION' and ROUTINE_SCHEMA like DB)
                      
                      UNION
                      
                      /* query for tables*/
                      (SELECT concat(ENGINE,' TABLE'), TABLE_NAME, TABLE_SCHEMA
                      FROM information_schema.tables
                      WHERE table_type='BASE TABLE' and TABLE_SCHEMA like DB
                      GROUP BY ENGINE, TABLE_NAME);
                      
                      /* show gathered information from temporary table */
                      SELECT object_name,object_type,object_schema
                      FROM objects;
                      
                      /* Prepare and show summary */
                      SELECT object_schema AS `DATABASE`,
                      SUM(IF(object_type like '%TABLE', 1, 0)) AS 'TABLES',
                      SUM(IF(object_type='VIEW', 1, 0)) AS 'VIEWS',
                      SUM(IF(object_type='TRIGGER', 1, 0)) AS 'TRIGGERS',
                      SUM(IF(object_type='FUNCTION', 1, 0)) AS 'FUNCTIONS',
                      SUM(IF(object_type='PROCEDURE', 1, 0)) AS 'PROCEDURES'
                      FROM objects
                      GROUP BY object_schema;
                      
                      END $$
                      
                      DELIMITER ;

Select all Open in new window

2. Usage:

call xplore('DATABASE-NAME');

Select all Open in new window

To list objects from all databases you may give 'DATABASE-NAME' as '%'.


Other ways to look for procedures and function is:

SHOW PROCEDURE STATUS; 
                      SHOW FUNCTION STATUS;

Select all Open in new window

Above will show procedures / functions from all databases. You may also filter it on database basis as follows:

SHOW PROCEDURE STATUS WHERE db LIKE 'database-name';

Select all Open in new window

To view object definitions following statements can be used:

SHOW CREATE FUNCTION function_name;
                      SHOW CREATE PROCEDURE procedure_name;
                      SHOW CREATE VIEW view_name;
                      SHOW CREATE TABLE table_name;
                      SHOW CREATE TRIGGER trigger_name ;

Select all Open in new window

Plenty of other information is available through the information_schema, which is not covered here.
For example, to gather further details regarding triggers we can utilize the following fields:


For more details on the above and other possible information, please refer to the information_schema tables along with the mysql documentation.  Additionally, here is a diagram for information_schema as a quick reference to help your understanding.