Our community of experts have been thoroughly vetted for their expertise and industry experience.
Browse All Articles > 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:
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.
A view is a named, derived table whose definition is a persistent part of the database.
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.
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_SCHEMAFROM information_schema.triggersWHERE TRIGGER_SCHEMA like DB)UNION/* query for views*/(SELECT 'VIEW', TABLE_NAME,TABLE_SCHEMAFROM information_schema.tablesWHERE table_type='VIEW' and TABLE_SCHEMA like DB)UNION/* query for procedure*/(SELECT 'PROCEDURE', SPECIFIC_NAME, ROUTINE_SCHEMAFROM information_schema.routinesWHERE routine_type='PROCEDURE' and ROUTINE_SCHEMA like DB)UNION/* query for function*/(SELECT 'FUNCTION', SPECIFIC_NAME, ROUTINE_SCHEMAFROM information_schema.routinesWHERE routine_type='FUNCTION' and ROUTINE_SCHEMA like DB)UNION/* query for tables*/(SELECT concat(ENGINE,' TABLE'), TABLE_NAME, TABLE_SCHEMAFROM information_schema.tablesWHERE table_type='BASE TABLE' and TABLE_SCHEMA like DBGROUP BY ENGINE, TABLE_NAME);/* show gathered information from temporary table */SELECT object_name,object_type,object_schemaFROM 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 objectsGROUP BY object_schema;END $$DELIMITER ;