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 ;