<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
10,435 Points
4,335 Views
1 Endorsement
Last Modified:
Approved
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 ;

Open in new window


2. Usage:
call xplore('DATABASE-NAME');

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;

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

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 ;

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:

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.

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.
1
Author:theGhost_k8
0 Comments

Featured Post

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Next Article:

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month