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

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 ;

2. Usage:

call xplore('DATABASE-NAME');

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;

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

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 ;

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:

 EVENT_OBJECT_SCHEMA & EVENT_OBJECT_TABLE - contains database and table name with which the trigger is associated.

 EVENT_MANIPULATION -  contains one of the values INSERT, DELETE, or UPDATE on which the trigger is activated.